SQL Server SELECT INTO @variable? [英] SQL Server SELECT INTO @variable?

问题描述

我在我的 Sql (2008) Stored Procs 之一中有以下代码，它执行得非常好:

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

所以你们的问题是有没有可能做一些类似的事情:

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

以便我可以在其他以下语句中重用内存中的这些数据?SQL Server 符合上述语句，但是我不想创建单独的变量并通过针对同一个表的单独 SELECT 语句初始化它们中的每一个....啊！！！

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?

推荐答案

您不能 SELECT .. INTO .. a TABLE VARIABLE.您能做的最好的事情是先创建它，然后再插入它.你的第二个片段必须是

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

这篇关于SQL Server SELECT INTO @variable?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！