How to insert multiple records and get the identity value?

sql

bulk-insertion

identity-values

cursor-free

byAnton Shumikhin·Dec 4, 2024

When it comes to inserting multiple records and retrieving their generated identities, you can use the OUTPUT clause in SQL Server:

INSERT INTO MyTable (Col1, Col2)
OUTPUT INSERTED.ID
VALUES ('Val1', 'Val2'), ('Val3', 'Val4');

In PostgreSQL, you will simply attach a RETURNING clause:

INSERT INTO MyTable (Col1, Col2)
VALUES ('Val1', 'Val2'), ('Val3', 'Val4')
RETURNING ID;

As for MySQL, after a bulk insert, call for the last inserted ID and affected rows or employ a transaction to retrieve each new ID.

Inserting and storing identity values

Let's say you wish to utilize these identity values for further operations. You can store them using a table variable:

DECLARE @Identities TABLE (ID INT);

-- Just casually holding on to those precious identity values. No big deal.
INSERT INTO MyTable (Col1, Col2)
OUTPUT INSERTED.ID INTO @Identities
VALUES ('Val1', 'Val2'), ('Val3', 'Val4');

-- Now, @Identities is ready to be invited to the party

This technique recycles the identity values, allowing you to use them multiple times throughout the script.

Cursor-free bulk insertion

Bidding adieu to cursors can significantly speed up bulk insert performance. The OUTPUT clause is your golden ticket to cursor-free operations:

-- Sayonara, Cursors!
INSERT INTO TableA (Column)
OUTPUT INSERTED.ID, INSERTED.Column -- Got you, identity and column values!
SELECT Column FROM TableB; -- Skrrt, skrrt from TableB to TableA

This simple yet powerful statement efficiently hauls records from TableB to TableA, capturing identities with absolute 0 cursor interference.

Post-insertion synchronization

After the records land in TableA, there might be a need to align these fresh identity values with their counterparts in TableB. Let's give it a whirl:

-- Mapping masterclass. Starts, NOW!
DECLARE @MapTable TABLE (OldID INT, NewID INT);

-- Remember, sharing is caring!
INSERT INTO TableA (Column)
OUTPUT TableB.ID, INSERTED.ID INTO @MapTable(OldID, NewID)
SELECT Column FROM TableB;

-- Looking lean with those new identities, TableB!
UPDATE TableB
SET TableB.NewId = MT.NewID
FROM @MapTable MT
WHERE TableB.ID = MT.OldID;

This showcases how to update TableB with fresh identities from TableA without cursors. Identity mapping at its finest!

Watch out! Potential pitfalls

Working with the OUTPUT clause can have its own fair share of complications, such as:

Triggers: They might interfere with the OUTPUT clause. So, keep an eye out!
Permissions: Make sure you have all the necessary permissions to party with the OUTPUT clause.
Transaction Scope: In a transaction scenario, remember, the rollbacks can directly influence the final identity values.