CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
bleepzter
bleepzter

Reputation: 10035

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?

Upvotes: 347

Views: 1074843

Answers (7)

Victor Ribeiro da Silva Eloy
Victor Ribeiro da Silva Eloy

Reputation: 4159

You can do this:

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

then later

SELECT CustomerId FROM #tempCustomer

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

Upvotes: 45

Rodrigo
Rodrigo

Reputation: 31

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!

Upvotes: 3

koushik veldanda
koushik veldanda

Reputation: 5

"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

Upvotes: -2

dougajmcdonald
dougajmcdonald

Reputation: 20067

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

Upvotes: 665

Valamas
Valamas

Reputation: 24759

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.

Upvotes: 4

Pat L
Pat L

Reputation: 538

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

Upvotes: 21

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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

Upvotes: 248

Related Questions