SQL Server SELECT INTO @variable？

SQL Server SELECT INTO @variable?

提问by bleepzter

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine:

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

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

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

Any suggestions on how to achieve something along the lines without multiple queries against the same table?

关于如何在没有针对同一个表的多个查询的情况下实现某些目标的任何建议？

采纳答案by RichardTheKiwi

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

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

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

回答by dougajmcdonald

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

如果那是你所追求的类型

回答by Victor Ribeiro da Silva Eloy

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

您不需要声明#tempCustomer 的结构

回答by Pat L

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

回答by Valamas

Sounds like you want temp tables. http://www.sqlteam.com/article/temporary-tables

听起来你想要临时表。 http://www.sqlteam.com/article/temporary-tables

Note that #TempTable is available throughout your SP.

请注意，#TempTable 在整个 SP 中都可用。

Note the ##TempTable is available to all.

请注意，##TempTable 可供所有人使用。

回答by Rodrigo

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:

到目前为止，我所做的是将整个 SQL 代码与要使用的变量连接起来。像这样：

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!

我希望它有所帮助，但如果代码太大可能会很麻烦，所以如果你找到了更好的方法，请分享！

回答by koushik veldanda

"SELECT *
  INTO 
    @TempCustomer 
FROM 
    Customer
WHERE 
    CustomerId = @CustomerId"

Which means creating a new @tempCustomertablevariable and inserting data FROM Customer. You had already declared it above so no need of again declaring. Better to go with

这意味着创建一个新的表@tempCustomer变量并从客户插入数据。你已经在上面声明了，所以不需要再次声明。最好一起去

INSERT INTO @tempCustomer SELECT * FROM Customer