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

How do I get inserted row Id, along with related data back after insert without inserting the related data(插入后如何在不插入相关数据的情况下获取插入的行 ID 以及相关数据)
本文介绍了插入后如何在不插入相关数据的情况下获取插入的行 ID 以及相关数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有一组数据需要在表中生成一个新行.创建此行后,我需要在与此信息相关的单独表中附加元数据.也就是说,我需要先创建我的 [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 很容易(请参阅下面的查询).但是我很困惑如何将 personnumberfirstnamelastname 插入到这个临时表中,以便我可以继续插入相关数据.

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 以及相关数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Error casting varchar containing a number expressed in scientific notation to decimal(将包含以科学记数法表示的数字的 varchar 转换为十进制时出错)
Calculate cumulative product value(计算累积产品价值)
SQL Server loop for changing multiple values of different users(用于更改不同用户的多个值的 SQL Server 循环)
SQL Server 2008: Can a multi-statement UDF return a UDT?(SQL Server 2008:多语句 UDF 能否返回 UDT?)
Merge columns in sql(合并sql中的列)
SQL Column is invalid in ORDER BY, not contained in aggregate or GROUP BY(SQL 列在 ORDER BY 中无效,未包含在聚合或 GROUP BY 中)