問題描述

我有一組數據需要在表中生成一個新行.創建此行后，我需要在與此信息相關的單獨表中附加元數據.也就是說，我需要先創建我的 [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

這篇關于插入后如何在不插入相關數據的情況下獲取插入的行 ID 以及相關數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！