sql - T-SQL 输出插入子句 - 访问不在插入/删除表中的数据

Question

我想从 INSERT 语句中使用的 SELECT 语句的源表中收集一个值，该值未插入到目标表中

我正在使用 Microsoft SQL Server 2017

我认为以下代码解释了我正在尝试做的事情:只需剪切并粘贴到 SSMS 中即可重现错误

DECLARE @CrossRef TABLE (
    MyTable_ID INT,
    C_C VARCHAR(10)
    );
DECLARE @MyData TABLE (
    A VARCHAR(10),
    B VARCHAR(10),
    C VARCHAR(10) );
INSERT INTO @MyData (A, B, C)
VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');

DECLARE @MyTable TABLE (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    A VARCHAR(10),
    B VARCHAR(10) );

INSERT INTO @MyTable (A, B)
OUTPUT INSERTED.Id, MyData.C
INTO @CrossRef (MyTable_ID, C_C)
SELECT A, B 
FROM @MyData AS MyData 

-- Error: The multi-part identifier "MyData.C" could not be bound.

-- DESIRED OUTPUT
SELECT * FROM @MyTable 
/*
ID    A    B
----------
1    A1    B1
2    A2    B2
3    A3    B3
*/
SELECT * FROM @CrossRef
/*
MyTable_ID C_C 
---------------
1            C1
2            C2
3            C3
*/

OUTPUT 子句无法访问不在 INSERTED 或 DELETED 内部表中的任何内容 - 这是错误的原因。然而这个例子Microsoft T-SQL OUTPUT CLAUSE (尽管关于 DELETED)似乎建议您可以访问其他表。

注意 - 该示例已被高度简化以使问题尽可能清楚通过其他方式获得所需的输出似乎微不足道，但与生产中的任何事情一样，实际情况要复杂得多

Answer 1

使用 MERGE 语句 - 正如 Tab Alleman 建议的那样，这里是解决方案:

DECLARE @CrossRef TABLE (
    MyTable_ID INT,
    C_C VARCHAR(10)
    );
DECLARE @MyData TABLE ( 
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    A VARCHAR(10),
    B VARCHAR(10),
    C VARCHAR(10) );
INSERT INTO @MyData (A, B, C)
VALUES ('A1', 'B1', 'C1'), ('A2', 'B2', 'C2'),('A3', 'B3', 'C3');

DECLARE @MyTable TABLE (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    A VARCHAR(10),
    B VARCHAR(10) );

-- MERGE statement does UPDATE where join condition exists and INSERT where it does not
MERGE @MyTable
USING (SELECT A, B, C FROM @MyData) AS [Source]
ON (1=0) -- join never true so everything inserted, nothing updated
WHEN NOT MATCHED THEN
    INSERT (A, B)
    VALUES ([Source].A, [Source].B)
    OUTPUT INSERTED.Id, [Source].C
    INTO @CrossRef (MyTable_ID, C_C);

SELECT * FROM @MyData
SELECT * FROM @MyTable 
SELECT * FROM @CrossRef