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?

"to create separate variables and initialize each one of them via a separate SELECT statement" - why would you need to do that? declare @t table once, and if you need to reuse it, fire a DELETE @TempCustomer before inserting into it again — RichardTheKiwi
– RichardTheKiwi, Commented Jan 28, 2011 at 1:40
You can use the Table Variable - mssqltips.com/sqlservertip/6039/… — AquaAlex
– AquaAlex, Commented Feb 25, 2022 at 8:30

dougajmcdonald · Accepted Answer · 2011-08-30 16:35:25Z

674

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

answered Aug 30, 2011 at 16:35

dougajmcdonald

20.2k12 gold badges61 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

capitano666 Over a year ago

Best answer imho, but what happens if the results are more than one?

Smandoli Over a year ago

If the results are more than one, you will get one of the available values. That could make for an interesting puzzle! See mssqltips.com/sqlservertip/1888/…

Adrian Over a year ago

To force the query to return a single row use SELECT TOP 1

dougajmcdonald Over a year ago

@Adrian Yes, the above query assumes you've selected a single row. You could also use ordering and aggregate functions if you want more fancy logic.

Tyler Over a year ago

In case if you aren't using the database type that the OP is using, not all support TOP 1 as Adrian mentioned. SQL Server / MS Access use TOP, MySQL uses LIMIT, and Oracle uses ROWNUM. See w3schools.com/sql/sql_top.asp for more information.

|

RichardTheKiwi · Accepted Answer · 2011-01-28 01:34:11Z

250

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

answered Jan 28, 2011 at 1:34

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

3 Comments

Michael Krauklis Over a year ago

Should note that this works just fine in a CTE context as well. Bazinga!

KSwift87 Over a year ago

Anyone have an answer as to why one cannot select into a table variable like you can with an actual temporary table?

Justin Rowe Over a year ago

Why it's not possible? Probably to prevent the possibility of blowing up your database server with out of memory exception. The DB server needs to be a very stable process...

ViRuSTriNiTy · Accepted Answer · 2023-09-18 16:09:52Z

45

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

edited Sep 18, 2023 at 16:09

ViRuSTriNiTy

5,2052 gold badges35 silver badges63 bronze badges

answered Jan 30, 2011 at 0:45

Victor Ribeiro da Silva Eloy

4,1793 gold badges30 silver badges34 bronze badges

3 Comments

Martin Smith Over a year ago

@webturner Neither of those points are true. Temp tables aren't scoped outside of the proc and table variables aren't any more "memory only" than temp tables.

ViRuSTriNiTy Over a year ago

Don't forget to add a DROP TABLE #tempCustomer when #tempCustomer is not needed anymore otherwise the next select will cause a #tempCustomer already exists error

Shahar Shokrani Over a year ago

@ViRuSTriNiTy It is only visible to the current session. Once the session ends or the connection is terminated, this table is automatically dropped.

Pat L · Accepted Answer · 2011-01-28 01:34:29Z

21

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

answered Jan 28, 2011 at 1:34

Pat L

5382 silver badges7 bronze badges

1 Comment

r2evans Over a year ago

Due to a redirect from dbforums, that "good examples" link has transformed into an ad. Is there a better link for what you intended?

Valamas · Accepted Answer · 2011-01-28 01:35:09Z

4

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.

answered Jan 28, 2011 at 1:35

Valamas

24.8k25 gold badges111 silver badges181 bronze badges

4 Comments

RichardTheKiwi Over a year ago

##temptable - until the owner(creator) deletes it or gets disconnected

bleepzter Over a year ago

I don't want temp tables. Temp tables are expensive and slow. I just want to keep a bit of data for a specific record for a small amount of time and make it available to multiple sql statements, without subsequent lookups. Since Table Variables have scope within the stored proc they were defined in, they are the perfect solution for storing tuppled data for that one stored proc call...

bleepzter Over a year ago

I also forgot to mention that the DB resides in Azure.... I don't want to introduce a mess out of managing temp tables.

ARLibertarian Over a year ago

Variable table is the way to go. declare @varTable Table (....)

Rodrigo · Accepted Answer · 2019-03-21 23:35:16Z

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!

James in Indy · Accepted Answer · 2025-03-31 14:36:59Z

0

Another option is to set a default for your variables. This way you don't need to create a temp table or execute multiple statements against the database. This worked for me on a query where I only needed 1 field; I don't know if this will translate to multiple fields.

DECLARE @Result CHAR(1) = 'N'; -- Default if no row is found
SELECT TOP(1) @Result = Field FROM Table WHERE whatever
SELECT @Result

answered Mar 31 at 14:36

James in Indy

2,8942 gold badges25 silver badges26 bronze badges

Comments

Taryn East · Accepted Answer · 2015-01-08 07:07:23Z

-2

"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

edited Jan 8, 2015 at 7:07

Taryn East

27.8k9 gold badges88 silver badges110 bronze badges

answered Jan 8, 2015 at 6:57

koushik veldanda

51 bronze badge

1 Comment

Johan Boulé Over a year ago

It doesn't work. You still need to declare the table variable beforehand.

Collectives™ on Stack Overflow

SQL Server SELECT INTO @variable?

8 Answers 8

6 Comments

3 Comments

3 Comments

1 Comment

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

6 Comments

3 Comments

3 Comments

1 Comment

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related