Get the last inserted row ID (with SQL statement)

sql

output-clause

scope-identity

sql-server

byNikita Barsukov·Dec 7, 2024

Fetch the last inserted ID with SCOPE_IDENTITY() in MS SQL Server, LAST_INSERT_ID() in MySQL, or RETURNING in PostgreSQL.

MS SQL Server:

INSERT INTO YourTable (...) VALUES (...); 
-- Like the light at the end of a tunnel, find your ID
SELECT SCOPE_IDENTITY();

MySQL:

INSERT INTO YourTable (...) VALUES (...); 
-- Poof! And here's your ID, just like magic!
SELECT LAST_INSERT_ID();

PostgreSQL:

-- Everyone loves a gift! Here's your ID wrapped with your data
INSERT INTO YourTable (...) VALUES (...) RETURNING ID;

SCOPE_IDENTITY() - The SQL Server superhero

In the landscape of MS SQL Server, SCOPE_IDENTITY() emerges as the superhero. This function returns the last inserted identity value specifically within your current scope. A valuable asset when @@IDENTITY might retrieve an ID generated by a trigger or a different session. Who needs villains when we’ve got heroes like these?

OUTPUT clause - The SQL Server sidekick

It's not just superheroes in SQL Server. The OUTPUT clause is the dependable sidekick, capable of instantly outputting inserted IDs, even for multi-row inserts. Think of it as your very own SQL Robocop, multitasking at its finest!

-- Robocop in action - catching all bad (new) IDs  
INSERT INTO YourTable (...)
OUTPUT Inserted.ID
VALUES (...);

A team for multi-row inserts

Handling multiple row inserts? The dynamic duo of OUTPUT and SCOPE_IDENTITY() shines through. As mentioned, SCOPE_IDENTITY() might not cut it here but our trusty sidekick OUTPUT ensures you get the correct set of identity values.

Navigating edge cases

Adventuring through the SCOPE_IDENTITY() post an INSERT is indeed a safe, but remember, even in SQL land, you should always beware. In distributed transactions, it might just lead you astray. For such mysteries, XACT_STATE() or alternative techniques could be your guiding star.

Robust ID retrieval rules of thumb

Nothing beats the classics! Explicitly define identity columns, avoid relying on side effects of triggers for ID generation. Logging or debugging with OUTPUT clause? Keep an eye on performance, nobody loves a slow app, right?

Bring theory to life with examples

Our SQL superheroes in action, showcasing OUTPUT with a table variable:

-- Your personal ID vault
DECLARE @InsertedIDs TABLE (ID INT);

-- Store new IDs in your ID vault
INSERT INTO YourTable (...)
OUTPUT Inserted.ID INTO @InsertedIDs
VALUES (...);

-- Open your vault and voila, there are your IDs!
SELECT ID FROM @InsertedIDs;

Need the ID right after you inserted a single row? SCOPE_IDENTITY() is your guy:

-- An ID placeholder
DECLARE @LastID INT;

-- Get your ID placeholder ready
INSERT INTO YourTable (...)
VALUES (...);

-- Time to fill up the placeholder
SELECT @LastID = SCOPE_IDENTITY();
-- @LastID is now fully loaded and ready for action.

A quick word to the wise

While SCOPE_IDENTITY() is a superhero, even heroes face challenges. After bulk inserts, for example, it might not function as expected. Just like spinach for Popeye, thorough testing strengthens your applications in complex transactional environments.

explain-codes / Sql / Get the last inserted row ID (with SQL statement)

Linked

Sql Server - Return value after INSERT



How to get the identity of an inserted row?



Inserting rows into a table with one IDENTITY column only



How to insert multiple records and get the identity value?



Sql Identity (autonumber) is Incremented Even with a Transaction Rollback

