SQL Server中关于 INSERTED.Id 与 SCOPE_IDENTITY() 获取新增数据的Id

前景：

通过程序将一个数据库的主表+子表的数据迁移至新的数据库，表的id未自增长，源数据存在删除的情况，所以需要获取插入数据新增id和旧id的比对关系数据

通过如下语句来获取新旧id

BEGIN TRY  BEGIN TRANSACTION;DECLARE @Map TABLE (OldId int, Id int);--临时表存储新旧idINSERT INTO SupportGroup (CompanyId,GroupCode,GroupName,Description,Status,Type,CreationDate,CreatedBy,UpdatedDate,UpdatedBy)OUTPUT INSERTED.Id,86 INTO @Map(Id,OldId)VALUES (N'86',N'T-86',N'全部权限',N'',1,1,'2023/9/21 10:09:32',N'TEST01','2025/9/16 10:45:00',N'TEST01');INSERT INTO SupportGroup (CompanyId,GroupCode,GroupName,Description,Status,Type,CreationDate,CreatedBy,UpdatedDate,UpdatedBy)OUTPUT SCOPE_IDENTITY(),87 INTO @Map(Id,OldId)VALUES (N'87',N'T-87',N'VND全部',N'',1,1,'2023/9/21 14:04:44',N'TEST01','2023/9/21 14:04:44',N'TEST01');INSERT INTO SupportGroup (CompanyId,GroupCode,GroupName,Description,Status,Type,CreationDate,CreatedBy,UpdatedDate,UpdatedBy)OUTPUT SCOPE_IDENTITY(),108 INTO @Map(Id,OldId)VALUES (N'108',N'T-108',N'wl_test',N'测试222',1,1,'2025/8/21 11:55:57',N'TEST01','2025/8/27 15:56:30',N'TEST01');COMMIT TRANSACTION;  -- 一切正常就提交
END TRY
BEGIN CATCHIF XACT_STATE() <> 0ROLLBACK TRANSACTION;  -- 出错就回滚-- 把错误抛出来（可选）DECLARE @errMsg nvarchar(4000) = ERROR_MESSAGE();RAISERROR (@errMsg, 16, 1);END CATCH;
SELECT OldId, Id FROM @Map;--获取新旧id

 

异常：

这个语句在数据库中执行没有任何问题，也能正常返回新旧id列表，但是在程序中执行这个语句就会出现一个问题

1.返回的一条新旧id数据中，新id是空的，且只有前面30 条<总共批量插入119条>被DBRearder读出来了，后面的89条出现异常（“数据提示不可用，不是有效的格式说明符”）

 

原因：

1.首先OUTPUT 子句里 SCOPE_IDENTITY() 语法上就被禁止

2.即使将来允许，它返回的也是“当前作用域里最后一条 INSERT 产生的 ID”，而 OUTPUT 子句是逐行触发的，语义上就会互相覆盖

3.OUTPUT 只认 INSERTED.Id

 

初衷：

鉴于之前的了解SCOPE_IDENTITY()获取的是作用域内新增数据的id，而 INSERTED.Id获取的是这张表最新新增数据的id，可能获取到的是其他途径新增的id

所以此处就使用SCOPE_IDENTITY()来获取新增数据的Id

 

特别的：

后了解到在OUTPUT 中使用INSERTED.Id 直接拿到“本条正在插入的行”的身份值，与并发、与顺序、与作用域都无关。

 

解决：

将SCOPE_IDENTITY()改为使用INSERTED.Id 获取本条新增数据的Id