SQL Server执行批量INSERT后如何返回所有插入行及自增id

SQL Server 2005及以上版本提供原生的OUTPUT子句，不需要额外编写循环、临时表关联逻辑，就能在批量插入完成后直接返回所有插入记录的自增ID和对应字段值。

直接返回结果集的写法 #

你只需要在INSERT语句里加上OUTPUT子句，指定要返回的字段即可，语句执行完成后会直接输出匹配的结果集，和你插入的行顺序一一对应：

INSERT INTO testResult ( name, value )
OUTPUT inserted.id, inserted.name, inserted.value
VALUES
('Helium', '.001'),
('Oxygen', '.19'),
('Palladium', '.054'),
('Carbon', '.21')
-- 追加剩余待插入的数值行即可

返回的结果里，id就是SQL Server为每一行自动生成的自增标识值，name和value就是对应行你插入的原始字段值。

需要留存返回结果的写法 #

如果你需要把返回的ID和字段值留存下来供后续SQL逻辑使用，可以把OUTPUT的结果写入表变量或者临时表：

-- 声明存储插入结果的表变量
DECLARE @InsertedRecords TABLE (
    id INT,
    name VARCHAR(100),
    value VARCHAR(50)
)

-- 批量插入时将生成的结果写入表变量
INSERT INTO testResult ( name, value )
OUTPUT inserted.id, inserted.name, inserted.value INTO @InsertedRecords
VALUES
('Helium', '.001'),
('Oxygen', '.19'),
('Palladium', '.054'),
('Carbon', '.21')
-- 追加剩余待插入的数值行即可

-- 后续任意逻辑都可以从表变量查询全量插入结果
SELECT * FROM @InsertedRecords

注意事项 #

• 不要用@@IDENTITY、SCOPE_IDENTITY()处理批量插入场景，这两个函数仅会返回最后一条插入记录生成的自增ID，无法拿到全量插入数据的ID值。
• 该方案是原生批量操作，性能远高于逐行插入再查询ID的实现，万级以内的数据量插入都不会有明显性能损耗。
• OUTPUT子句中引用插入后的字段值，必须加inserted.前缀访问虚拟表，和DML触发器中访问变更后数据的规则一致。

内容的提问来源于stack exchange，提问作者SkyeBoniwell