Update temp table with identity after insert

Question

I have a temp table that looks like this:

FirstName
LastName
DOB
Sex
Age
ExternalID

In my stored procedure I'm inserting these values into a regular table that has the following structure:

ID  identity(1,1)
FirstName
LastName

So, I do this:

Insert into myTable
select FirstName, LastName from TempTable

During the insert I need to insert primary key from main table back into temp table "ExternalID" column. How can this be achieved?

I tried using OUTPUT statement but it only allows to insert to a separate table and then I have no way to map back to temp table

I need to insert generated IDs to column ExternalID in temp table right after the insert. FirstName and LastName are not unique.

One possible solution would be to use loop and insert one row at a time. This way, I can update temp table row with scope_identity(). But I want to avoid using loops.

@maSTAShuFu I need to use extra columns from this table + associated IDs later in my stored procedure — user194076, Jan 23 '18 at 21:45
you use the temptable as a placeholder but not as the main source of truth — RoMEoMusTDiE, Jan 23 '18 at 21:47
Possible duplicate of [Best way to get identity of inserted row?](https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Morpheus, Jan 23 '18 at 21:48
@maSTAShuFu, main table doesn't have all of the columns that are in temp table. — user194076, Jan 23 '18 at 21:51
If You can only insert a row with no column-name, It should exist all column value in order. — Başar Kaya, Jan 23 '18 at 21:59
If you can add an identity column to your temp table as well, you can abuse `MERGE` so that your `OUTPUT` clause can reference both identity columns. It's then a trivial `UPDATE` to the temp table. — Damien_The_Unbeliever, Jan 24 '18 at 11:32
Here's [one example](https://stackoverflow.com/a/10950418/15498) of the `MERGE` abuse technique. There are plenty on this site (I know I've written answers using it once or twice myself but didn't find one immediately) — Damien_The_Unbeliever, Jan 24 '18 at 11:34
@Damien_The_Unbeliever, thanks Damien, this looks promising. — user194076, Jan 25 '18 at 22:49

bendodge · Answer 1 · 2018-07-10T20:20:37.027

Try using MERGE instead of INSERT.

MERGE allows you to output a column you didn't insert, such as an identifier on your temp table. Using this method, you can build another temporary table that maps your temp table to the inserted rows (named @TempIdTable in the sample below).

First, give #TempTable its own primary key. I'll call it TempId. I'll also assume you have a column on #TempTable to store the returned primary key from MyTable, ID.

--Make a place to store the associated ID's
DECLARE @TempIdTable TABLE
    ([TempId] INT NOT NULL
    ,[ID] INT NOT NULL)

--Will only insert, as 1 never equals 0.
MERGE INTO myTable
USING #TempTable AS tt
    ON 1 = 0
WHEN NOT MATCHED
    THEN
        INSERT ([FirstName]
                ,[LastName])
        VALUE (t.[FirstName]
                ,t.[LastName])
OUTPUT tt.[TempId], inserted.[ID] --Here's the magic
INTO @TempIdTable

--Associate the new primary keys with the temp table
UPDATE #TempTable
SET [ID] = t.[ID]
FROM @TempIdTable t
WHERE #TempTable.[TempId] = t.[TempId]

I was working on a similar issue and found this trick over here: Is it possible to for SQL Output clause to return a column not being inserted?

Here's the full code I used in my own testing.

CREATE TABLE [MQ]
    ([MESSAGEID] INT IDENTITY PRIMARY KEY
    ,[SUBJECT] NVARCHAR(255) NULL);

CREATE TABLE [MR]
    ([MESSAGESEQUENCE] INT IDENTITY PRIMARY KEY
    ,[TO] NVARCHAR(255) NOT NULL
    ,[CC] NVARCHAR(255) NOT NULL
    ,[BCC] NVARCHAR(255) NOT NULL);

CREATE TABLE #Messages (
    [subject] nvarchar(255) NOT NULL
    ,[to] nvarchar(255) NOT NULL
    ,[cc] nvarchar(255) NULL
    ,[bcc] nvarchar(255) NULL
    ,[MESSAGEID] INT NULL
    ,[sortKey] INT IDENTITY PRIMARY KEY
    );

INSERT INTO #Messages
VALUES ('Subject1','to1','cc1','bcc1', NULL)
    ,('Subject2','to2', NULL, NULL, NULL);

SELECT * FROM #Messages;

DECLARE @outputSort TABLE (
    [sortKey] INT NOT NULL
    ,[MESSAGEID] INT NOT NULL
    ,[subject] NVARCHAR(255)
    );

MERGE INTO [MQ]
USING #Messages M
    ON 1 = 0
WHEN NOT MATCHED
    THEN
        INSERT ([SUBJECT])
        VALUES (M.[subject])
OUTPUT M.[SORTKEY]
    ,inserted.[MESSAGEID]
    ,inserted.[SUBJECT]
INTO @outputSort;

SELECT * FROM @outputSort;

SELECT * FROM [MQ];

UPDATE #Messages
SET MESSAGEID = O.[MESSAGEID]
FROM @outputSort O
WHERE #Messages.[sortKey] = O.[sortKey];

SELECT * FROM #Messages;

DROP TABLE #Messages;

score 0 · Answer 2 · answered Jan 24 '18 at 11:28

0

As you said, FirstName and LastName are not unique. This means you cannot use a trigger because there can be the same FirstName + LastName so you cannot join on them.

But you can do the inverse thing: first update your temp table ExternalID (I suggest you to use sequence object and just do update #t set ExternalID = next value for dbo.seq1;) and then just insert your rows including ExternalID into myTable. To be able to insert into identity field you can use set identity_insert myTable on or you can re-design your destination table to contain no identity at all as now you use sequence for the same purpose.

answered Jan 24 '18 at 11:28

sepupic

8,409
1
9
20

This is a good idea. The only problem I can think of is parallel sessions might have intersecting ids at the same time – user194076 Jan 25 '18 at 22:51
No, sequence does notgenerate the same id multiple times – sepupic Jan 26 '18 at 07:20

score 0 · Answer 3 · answered Feb 23 '18 at 08:57

We need a unique column for able to make the comparison at the update operation after the insert. That's why we are using ExternalID column temporarily. ExternalID updated by row_nubmber.

;WITH CTE AS
(
    SELECT *, RN = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM @TempTable
)
UPDATE CTE SET ExternalID = RN

We are keeping the output of the insert operation in a temp table. The trick is order by with ExternalID, it will help us for making the unique row number for same first and last name

DECLARE @output TABLE (
ID  INT,
FirstName VARCHAR(10),
LastName VARCHAR(10))

Insert into @myTable
OUTPUT inserted.ID, inserted.FirstName, inserted.LastName  INTO @output(ID, FirstName, LastName) 
select FirstName, LastName from @TempTable T 
order by ExternalID

For replacing the ExternalID column with inserted id value, we are making comparing with first name, last name and row number.

;WITH TMP_T AS(
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ExternalID) FROM @TempTable )
,OUT_T AS( 
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ID) FROM @output )
UPDATE TMP_T SET ExternalID = OUT_T.ID
FROM 
TMP_T INNER JOIN OUT_T ON 
    TMP_T.FirstName = OUT_T.FirstName 
    AND TMP_T.LastName = OUT_T.LastName 
    AND TMP_T.RN = OUT_T.RN

Sample Data:

DECLARE @TempTable TABLE (
FirstName VARCHAR(10),
LastName VARCHAR(10),
DOB VARCHAR(10),
Sex VARCHAR (10),
Age VARCHAR(10),
ExternalID INT)

INSERT INTO @TempTable VALUES
('Serkan1', 'Arslan1', 'A','M','1',NULL),
('Serkan2', 'Arslan2', 'B','M','1',NULL),
('Serkan3', 'Arslan', 'C','M','1',NULL),
('Serkan3', 'Arslan', 'D','M','1',NULL)

DECLARE @myTable TABLE (
ID  INT identity(100,1), -- started from 100 for see the difference
FirstName VARCHAR(10),
LastName VARCHAR(10))

Result:

MyTable

ID          FirstName  LastName
----------- ---------- ----------
100         Serkan1    Arslan1
101         Serkan2    Arslan2
102         Serkan3    Arslan
103         Serkan3    Arslan

TempTable

FirstName  LastName   DOB        Sex        Age        ExternalID
---------- ---------- ---------- ---------- ---------- -----------
Serkan1    Arslan1    A          M          1          100
Serkan2    Arslan2    B          M          1          101
Serkan3    Arslan     C          M          1          102
Serkan3    Arslan     D          M          1          103

I learned a lot from this answer, but matching based on first and last name is a showstopper. Names aren't unique enough. — bendodge, Jul 10 '18 at 18:16
@bendodge that's why I am using row_number. and it makes names and RN are unique. — Serkan Arslan, Jul 10 '18 at 18:54

Sal · Answer 4 · 2020-08-12T14:59:59.067

One way to do this is by duplicating the data into a second temp table like so:

SELECT *
INTO #TEMPTABLE
FROM (VALUES (1, 'Adam'), (2, 'Kate'), (3, 'Jess')) AS X (Id, Name)

SELECT TOP 0 CAST(NULL AS INT) AS IdentityValue, *
INTO #NEWTEMPTABLE
FROM #TEMPTABLE

CREATE TABLE #TABLEFORINSERT (
    IdentityColumn INT IDENTITY(1,1),
    Id INT,
    Name VARCHAR(255)
)

INSERT INTO #TABLEFORINSERT (Id, Name)
OUTPUT INSERTED.IdentityColumn, INSERTED.Id, Inserted.Name INTO #NEWTEMPTABLE
SELECT Id, Name FROM #TEMPTABLE

--New temp table with identity values
SELECT * FROM #NEWTEMPTABLE

Update temp table with identity after insert

4 Answers4