插入Identity后如何获取GlobalId及关联数据，批量关联Accounts与Metadata

你已经找对了思路，核心问题就是怎么把person CTE里的真实用户信息和生成的GlobalId一起存到临时表，而不是硬编码字符串。下面是调整后的批量处理代码，完美适配你200万初始数据和每日数千条的处理需求，全程用集合操作，完全不用游标：

优化后的完整代码 #

DECLARE @temp AS TABLE(
    [GlobalId] BIGINT,
    [Personnumber] NVARCHAR(100),
    [Firstname] NVARCHAR(100),
    [Lastname] NVARCHAR(100)
);

WITH person AS (
    SELECT 
        t.[Personnumber],
        t.[Firstname],
        t.[Lastname]
    FROM [temp].[RawRoles] t
    WHERE t.Personnumber NOT IN (
        SELECT i.Account FROM [security].[Accounts] i
    )
)
INSERT INTO [security].[Identities] ([Created], [Updated])
-- 这里直接引用person里的字段，就能把真实值输出到临时表
OUTPUT inserted.GlobalId, p.Personnumber, p.Firstname, p.Lastname INTO @temp
SELECT 
    GETUTCDATE(),  -- 填充Identities的Created字段
    GETUTCDATE(),  -- 填充Identities的Updated字段
    p.Personnumber,
    p.Firstname,
    p.Lastname
FROM person p;

-- 接下来用临时表的数据批量插入Accounts
INSERT INTO [security].[Accounts] ([GlobalId], [Account], [Created], [Updated])
SELECT 
    t.GlobalId,
    t.Personnumber,
    GETUTCDATE(),
    GETUTCDATE()
FROM @temp t;

-- 批量插入Metadata（假设表结构包含GlobalId、Key、Value等字段）
INSERT INTO [security].[Metadata] ([GlobalId], [Key], [Value], [Created], [Updated])
SELECT 
    t.GlobalId,
    'FirstName',
    t.Firstname,
    GETUTCDATE(),
    GETUTCDATE()
FROM @temp t
UNION ALL
SELECT 
    t.GlobalId,
    'LastName',
    t.Lastname,
    GETUTCDATE(),
    GETUTCDATE()
FROM @temp t;

关键调整说明 #

1. 捕获真实用户数据：
   我在INSERT的SELECT语句里，除了Identities需要的Created和Updated，还把person CTE的三个用户字段加了进去（虽然这些字段不会插入到Identities表，但可以在OUTPUT里引用）。这样OUTPUT就能直接拿到p.Personnumber这类真实值，而不是你之前硬编码的字符串。

2. 批量处理适配：
   去掉了原代码里的TOP 1，这样一次就能处理所有符合条件的记录，集合操作的效率比游标高太多，完全能hold住200万条的初始数据量。

3. 后续关联操作：
   临时表@temp现在同时有GlobalId和对应的用户信息，你可以直接基于它批量插入到Accounts和Metadata，全程都是高效的集合操作，不用循环。

额外性能小技巧 #

• 给[temp].[RawRoles].Personnumber和[security].[Accounts].Account加个非聚集索引，能大幅加快NOT IN的查询速度，尤其是处理大数据量的时候。
• 如果每日增量数据太多，怕一次性处理占资源，可以用分批处理，比如每次处理1000条，只需要在person CTE里加个分页逻辑：

WITH person AS (
    SELECT 
        t.[Personnumber],
        t.[Firstname],
        t.[Lastname]
    FROM [temp].[RawRoles] t
    WHERE t.Personnumber NOT IN (
        SELECT i.Account FROM [security].[Accounts] i
    )
    ORDER BY t.Personnumber
    OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY  -- 每次取1000条，调整OFFSET值循环处理
)
-- 后续插入逻辑不变

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