Insert

Question

Insert

Mike Levan

SSChampion

Points: 14201
More actions
July 11, 2008 at 9:58 am

#184598

can i do this insert
INSERT INTO @GroupId, @GroupName, @ZoneInstanceId, @ScoreType
SELECT ItemId,InspectionItemName,ZoneInstanceId,ScoreTypeId from InspectionGroup
ORDER BY InspectionItemName
here InspectionGroup is a CTE

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Noffer Ten Centuries Points: 1090 More actions · Answer 1

I haven't worked with common table expressions much, but wondering if you are trying to get the values into array variables?? The only way I've done that is through table variables, but if your are just wanting a specific row to be set tot he 4 variables use something like SELECT @var1 = Col1, @var2 = col2, ...

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

I think you'd

select @a = myval, @b-2 = myval2, etc.

from myCTE

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

If you're trying add these values to a CTE, wouldn't you use UNION? Probably in a second CTE.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

Mike Levan (7/11/2008)
can i do this insert
INSERT INTO @GroupId, @GroupName, @ZoneInstanceId, @ScoreType
SELECT ItemId,InspectionItemName,ZoneInstanceId,ScoreTypeId from InspectionGroup
ORDER BY InspectionItemName
here InspectionGroup is a CTE

No... You cannot INSERT into scalar variables... tell us what the entire problem is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 5

Yes it will surely work

create table aaaa(id int)

insert into aaaa values(1)

insert into aaaa values(2)

insert into aaaa values(3)

select * from aaaa

create table venkat(id int)

with cte as

(

select * from aaaa

)

insert into venkat(id)

select * from cte

select * from venkat

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

Good example of an Insert... that's not inserting into a scalar variable, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

Jeff is right. You cannot insert into scalars. you can reassign variables (as in my first post), but INSERT is only for Table(like) structures.