SQL Server中同时向两表插入数据并获取自增Order Id

可以通过表变量+OUTPUT子句+事务的方式实现一次性插入，同时保证操作的原子性（要么全部成功，要么全部回滚），具体实现如下：

示例代码 #

-- 声明表变量，用于存储插入订单后生成的自增ID
DECLARE @InsertedOrderIds TABLE (OrderId INT);

BEGIN TRANSACTION;

-- 插入订单记录，同时用OUTPUT捕获生成的自增OrderId存入表变量
INSERT INTO [Order] (name)
OUTPUT inserted.Id INTO @InsertedOrderIds(OrderId)
VALUES ('2024年夏季客户采购订单'); -- 替换为实际订单名称

-- 插入该订单对应的商品关联记录，直接引用表变量中的订单ID
INSERT INTO PRODUCT_ORDER (product_id, order_id)
VALUES 
(1001, (SELECT OrderId FROM @InsertedOrderIds)), -- 商品ID 1001
(1002, (SELECT OrderId FROM @InsertedOrderIds)); -- 商品ID 1002

COMMIT TRANSACTION;

关键说明 #

• OUTPUT inserted.Id会提取插入[Order]表时自动生成的自增ID，存入预先声明的表变量@InsertedOrderIds
• 事务BEGIN TRANSACTION和COMMIT TRANSACTION确保两个插入操作的原子性，避免出现只插入了订单但未插入关联商品的情况
• PRODUCT_ORDER的自增id字段由SQL Server自动生成，无需手动指定

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