2

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.

enter image description here

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

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 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.

JensB
  • 6,663
  • 2
  • 55
  • 94
  • 2
    Try to use MERGE. If I remember it right, MERGE can output the columns from both the target and the source columns – PeterHe Nov 29 '19 at 14:15
  • 2
    You can, yes @PeterHe . This [answer](https://stackoverflow.com/a/10950418/34848790), for example, shows how. – Thom A Nov 29 '19 at 14:32
  • @PeterHe Thanks for the tip, solved it using Merge as shown below. – JensB Nov 29 '19 at 14:48

1 Answers1

1

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

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
JensB
  • 6,663
  • 2
  • 55
  • 94