插入后如何在不插入相关数据的情况下获取插入的行 ID 以及相关数据

2021-09-10 00:00:00 tsql sql-server

我有一组数据需要在表中生成一个新行.创建此行后，我需要在与此信息相关的单独表中附加元数据.也就是说，我需要先创建我的 [Identity]，从行中取回 GlobalId，然后附加 [Accounts] 和 >[元数据]到它.

I have a set of data which need to result in a new row in a table. Once this row is created I need to attach metadata in separate tables related to this information. That is I need to create my [Identity] first, get the GlobalId back from the row, and then attach [Accounts] and [Metadata] to it.

插入数据并获取插入行的 ID 很容易(请参阅下面的查询).但是我很困惑如何将 personnumber、firstname 和 lastname 插入到这个临时表中，以便我可以继续插入相关数据.

Inserting data and getting the Id of the inserted row is easy enough (see query below). But I'm stumped as to how I get the personnumber, firstname, and lastname inserted into this temporary table as well so I can continue with inserting the related data.

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

;WITH person AS
(
    SELECT top 1
        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])
-- how do i get real related values here and not my hard coded strings?
OUTPUT inserted.GlobalId, 'personnumber', 'firstname', 'lastname' INTO @temp 
SELECT GETUTCDATE(), GETUTCDATE()
FROM person

附言背景故事.对我来说，身份只是我们将使用的全局 ID 的持有者，而不是其他系统中的实际个人号码(相当于社会安全号码)，这样只有一个位置具有敏感号码，并且可以关联多个帐户标识，例如社会安全编号或 AD 帐户到相同的全局 ID.

P.S. Backstory. Identities for me is just a holder of a global Id we will be using instead of actual personal numbers (equivalent of social security numbers) in other systems, this way only one location has sensitive numbers, and can relate multiple account identifications such as social security number or AD accounts to the same global id.

P.P.S 我更愿意避免使用游标，因为查询在第一次运行时将移动近 200 万条记录，每天移动数千条记录.

P.P.S I would prefer to avoid Cursors as the query is going to be moving around almost 2 million records on first run, and several thousand on a daily basis.

推荐答案

@PeterHe 给了我一个关于如何使用 MERGE

@PeterHe gave me an idea on how to solve this with MERGE

按如下方式工作.插入所有行后，我可以查询 @temp 以继续其余的插入.

Got it working as follows. When all rows have been inserted I can query @temp to continue the rest of the inserts.

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

;WITH person AS
(
    SELECT top 1
        t.[Personnumber]
        ,t.[Firstname]
        ,t.[Lastname]
    FROM [temp].[RawRoles] t
    WHERE t.Personnumber NOT IN 
        (
            SELECT i.Account FROM [security].[Accounts] i
        )
)

MERGE [security].[Identities] AS tar
USING person AS src
ON 0 = 1 -- all rows from src need to be inserted, ive already filtered out using CTE Query.
WHEN NOT MATCHED THEN
   INSERT
   (
     [Created], [Updated]
   )
   VALUES
   (
        GETUTCDATE(), GETUTCDATE()
   )
OUTPUT $action, inserted.GlobalId, src.[Personnumber], src.[Firstname], src.[Lastname]  INTO @temp;


SELECT * FROM @temp

相关文章