[sql] 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?

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.

"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

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!

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

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

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