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
