conditional insert to Table variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conditional insert to Table variables

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-09 : 13:32:34

Student_Score

RollNO Subject Score
10 HIST 80
20 HIST 90
30 HIST 80
10 GEO 80
20 GEO 90
30 GEO 80

Student_target

RollNO Subject Target
10 HIST 100
20 HIST 100
30 HIST 100
10 GEO 150
20 GEO 150
30 GEO 150

Set @Target='HIST'
Set @target = 100

Declare @Target as Table
(RollNo Varchar(5),
Subject Varchar(5),
Target int)

insert into @target
(select rollno,@subject,@target from Student_Score)

Insert into Student_target
select rollno,subject,score from @target

for inserting Target for 'GEO' , do i need to make another table variables.
How can i make use of a conditional statment to insert both HIST and GEO in one GO.

Chandragupta Mourya

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 14:48:05

Seems like you've misunderstood the assignment

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-09 : 14:49:38

I have understood correctly. Looks like you didnt understand the depth of the problem

Chandragupta Mourya

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 14:57:01

I'm not sure I understand your actual requirement. Yes, you can add another table variable, but you don;t need to. In fact you could do it without any table variable if you wanted (assuming I think I get what you want):

INSERT
	Student_Target
SELECT
	RollNO,
	[Subject],
	CASE
		WHEN [Subject] = 'HIST' THEN 100
		WHEN [Subject] = 'GEO' THEN 110
		ELSE 0 -- Unknown
	END as [Target]
FROM
	Student_Score
-- Might be optional
WHERE
	[Subject] IN ('HIST', 'GEO')

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-09 : 15:38:44

quote:
Originally posted by konark

I have understood correctly. Looks like you didnt understand the depth of the problem

Chandragupta Mourya

very true, this problem is too deep to understand

Set @Target='HIST'
Set @target = 100

Declare @Target as Table
(RollNo Varchar(5),
Subject Varchar(5),
Target int)

@target has multiple personality disorder

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-09 : 17:32:48

Lamprey , sorry i have to use Table variables , as the result set is too large.
I needed to know if i can avoid creating another table variable for another subject 'GEO' to insert/update target score for each rollNo.

Rohit Kumar. You know little knowledge is dangerous thing.
Instead of making out fun ,you could have worked on the solution .

Chandragupta Mourya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources