SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine:

 CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM RelatedItems WHERE CustomerId = @CustomerId ) DECLARE @TempItem TABLE ( ItemId uniqueidentifier, CustomerId uniqueidentifier, Description nvarchar(100), Type int, Username nvarchar(100), TimeStamp datetime ); INSERT INTO Item OUTPUT INSERTED.* INTO @TempItem SELECT NEWID(), @CustomerId, @Description, @Type, @Username, GETDATE() SELECT ItemId, CustomerId, @TopRelatedItemId, Description, Type, Username, TimeStamp FROM @TempItem END GO 

So the question for you guys is is there a possibility to do something along the lines of:

DECLARE @TempCustomer TABLE ( CustomerId uniqueidentifier, FirstName nvarchar(100), LastName nvarchar(100), Email nvarchar(100) ); SELECT CustomerId, FirstName, LastName, Email INTO @TempCustomer FROM Customer WHERE CustomerId = @CustomerId 

So that I could reuse this data from memory in other following statements? SQL Server throws a fit with the above statement, however i don't want to have to create separate variables and initialize each one of them via a separate SELECT statement against the same table.... UGH!!!

Any suggestions on how to achieve something along the lines without multiple queries against the same table?

7 Answers

If you wanted to simply assign some variables for later use, you can do them in one shot with something along these lines:

declare @var1 int,@var2 int,@var3 int; select @var1 = field1, @var2 = field2, @var3 = field3 from table where condition 

If that's the type of thing you're after

You cannot SELECT .. INTO .. a TABLE VARIABLE. The best you can do is create it first, then insert into it. Your 2nd snippet has to be

DECLARE @TempCustomer TABLE ( CustomerId uniqueidentifier, FirstName nvarchar(100), LastName nvarchar(100), Email nvarchar(100) ); INSERT INTO @TempCustomer SELECT CustomerId, FirstName, LastName, Email FROM Customer WHERE CustomerId = @CustomerId 

you can do this:

SELECT CustomerId, FirstName, LastName, Email INTO #tempCustomer FROM Customer WHERE CustomerId = @CustomerId 

then later

SELECT CustomerId FROM #tempCustomer 

you doesn't need to declare the structure of #tempCustomer

It looks like your syntax is slightly out. This has some good examples

DECLARE @TempCustomer TABLE ( CustomerId uniqueidentifier, FirstName nvarchar(100), LastName nvarchar(100), Email nvarchar(100) ); INSERT @TempCustomer SELECT CustomerId, FirstName, LastName, Email FROM Customer WHERE CustomerId = @CustomerId 

Then later

SELECT CustomerId FROM @TempCustomer 

Sounds like you want temp tables.

Note that #TempTable is available throughout your SP.

Note the ##TempTable is available to all.

I found your question looking for a solution to the same problem; and what other answers fail to point is a way to use a variable to change the name of the table for every execution of your procedure in a permanent form, not temporary.

So far what I do is concatenate the entire SQL code with the variables to use. Like this:

declare @table_name as varchar(30) select @table_name = CONVERT(varchar(30), getdate(), 112) set @table_name = 'DAILY_SNAPSHOT_' + @table_name EXEC(' SELECT var1, var2, var3 INTO '+@table_name+' FROM my_view WHERE string = ''Strings must use double apostrophe'' '); 

I hope it helps, but it could be cumbersome if the code is too large, so if you've found a better way, please share!

"SELECT * INTO @TempCustomer FROM Customer WHERE CustomerId = @CustomerId" 

Which means creating a new @tempCustomer tablevariable and inserting data FROM Customer. You had already declared it above so no need of again declaring. Better to go with

INSERT INTO @tempCustomer SELECT * FROM Customer