sql-server - 插入时输出插入和表值

Question

我正在插入到 MS SQL Server 2008 中的一个表中(它是同一个表中值的副本)并且想要获取插入的输出值。我想获取 select 语句的 id 值(下面示例中的 t.id)，INSERTED.id 工作得很好

create table tmp.tbl_inserted (fromId int, toId int)

INSERT INTO mytable (name)
OUTPUT t.id, INSERTED.id INTO tmp.tbl_inserted
SELECT t.name FROM mytable t

提前致谢

Answer 1

您不能直接从 INSERT 执行此操作:

from_table_name

Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

请注意，未提及 INSERT。

您要做的是作弊并使用MERGE:

MERGE INTO mytable m
USING (name,id FROM mytable) t ON 1=0
WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
OUTPUT t.id, INSERTED.id INTO tmp.SizeCurveGroup_inserted
;