SQL Server批量插入关联表数据如何获取自增ID关联外键

核心思路 #

利用SQL Server的MERGE语句配合OUTPUT子句，批量插入User表的同时捕获自增UserID和对应角色的映射关系，再批量插入UserRole表，全程无循环，性能优于逐行操作。 #

具体实现步骤 #

1. 定义表值参数类型（若未提前定义） #

你可以直接用一个表值参数同时传递用户名和对应角色的映射，无需拆分两个参数：

CREATE TYPE UserWithRoleType AS TABLE (
    UserName NVARCHAR(50) NOT NULL,
    RoleName NVARCHAR(50) NOT NULL
);

2. 执行批量插入逻辑 #

-- 声明表变量存储插入User表后生成的ID和角色的映射
DECLARE @UserIDMapping TABLE (
    GeneratedUserID INT NOT NULL,
    RoleName NVARCHAR(50) NOT NULL
);

-- 假设你传入的表值参数变量名为 @InputUserRoles，类型为上述定义的UserWithRoleType
MERGE INTO [User] u
USING @InputUserRoles input
-- 恒假条件，强制所有数据走INSERT分支，仅用于获取原参数和插入结果的关联
ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (UserName) VALUES (input.UserName)
-- 捕获生成的UserID和对应的角色，存入映射表变量
OUTPUT inserted.ID, input.RoleName INTO @UserIDMapping(GeneratedUserID, RoleName);

-- 批量插入UserRole表
INSERT INTO UserRole (UserID, RoleName)
SELECT GeneratedUserID, RoleName FROM @UserIDMapping;

说明 #

• [User]加方括号是因为User是SQL Server的保留关键字，避免语法报错。
• 普通INSERT语句的OUTPUT子句只能返回插入后的表字段，无法关联原表值参数中的角色字段，MERGE语句可以突破这个限制。
• 全程为集合操作，没有循环，性能和单次插入单表相当，远高于逐行循环插入。

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