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?
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