向temp表插入具有多个结果的动态变量

Question

我想从动态变量中插入多个记录到临时表中，但是我无法成功。使用INSERT无法获得成功。你知道怎么做吗？

我得到了以下错误：A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

代码的部分：

-- Creating temp table
CREATE TABLE #tempTest
(
    one DATE,
    two ID,
    three NVARCHAR(60)
)
DECLARE @test NVARCHAR(MAX)
-- Selecting 3 records (Date, Id, Email) to insert into temp table
SELECT @test = Date, Id, Email --throws error in this line
FROM   (SELECT [Date], [Id], [Email]
        FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY FI.Id ASC) AS 'RowNum', CONVERT(DATE, FormV.[DateUpdated])   AS  [Date], FI.Id AS [Id]
            FROM        FormI                           AS  FI
            INNER JOIN  FormV                           AS  FormV
            ON          FI.FormVId  = FormV.Id
            INNER JOIN  NavArt                              AS  NA
            ON          FI.ArtId        = NA.Id
            WHERE       FI.WorkShiftId      = 10 
            ) withRownNum
        WHERE RowNum = 1

    ) a

-- Here I'm trying to insert these records to temp table, but unsuccessfully
INSERT INTO #tempTest VALUES(@colsConversion)

Answer 1

你不需要变量。试试这个：

CREATE TABLE #tempTest
(
    one DATE,
    two ID,
    three NVARCHAR(60)
)
INSERT INTO #tempTest
SELECT  Date, Id, Email 
FROM   (SELECT [Date], [Id], [Email]
        FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY FI.Id ASC) AS 'RowNum', CONVERT(DATE, FormV.[DateUpdated])   AS  [Date], FI.Id AS [Id]
            FROM        FormI                           AS  FI
            INNER JOIN  FormV                           AS  FormV
            ON          FI.FormVId  = FormV.Id
            INNER JOIN  NavArt                              AS  NA
            ON          FI.ArtId        = NA.Id
            WHERE       FI.WorkShiftId      = 10 
            ) withRownNum
        WHERE RowNum = 1

    ) a

Answer 2

问题是，您不能有一个select，其中您分配一些列给一个变量，但不是所有的。我不明白为什么要分配任何变量，但也可以为@id和@email设置变量。

如果您想创建一个临时表，为什么不直接这样做呢？

    SELECT [Date], [Id], [Email]
    INTO #temptest
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY FI.Id ASC) AS RowNum, CONVERT(DATE, FormV.[DateUpdated])   AS  [Date], FI.Id AS [Id]
        FROM        FormI                           AS  FI
        INNER JOIN  FormV                           AS  FormV
        ON          FI.FormVId  = FormV.Id
        INNER JOIN  NavArt                              AS  NA
        ON          FI.ArtId        = NA.Id
        WHERE       FI.WorkShiftId      = 10 
        ) withRownNum
    WHERE RowNum = 1;

注意:可能有更简单的方法来编写这个子查询(例如使用top )，但这是另一个问题的答案。