SQL Server SELECT INTO variable

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Sounds like you want temp tables  http   www sqlteam com article temporary-tables  Note that  TempTable is available throughout your SP   Note the   TempTable is available to all

[sql] SQL Server SELECT INTO @variable?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008