Sql Server - Return value after INSERT

sql

best-practices

sql-server

output-clause

byNikita Barsukov·Sep 30, 2024

Fetch the last inserted identity value directly using SCOPE_IDENTITY() after your INSERT query:

INSERT INTO YourTable (Column1, Column2)
VALUES ('Value1', 'Value2');

-- Select the id like nobody's watching but SQL Server is.
SELECT SCOPE_IDENTITY();

For all-inclusive results that capture all inserted values, utilize the OUTPUT clause in conjunction with a table variable:

DECLARE @CapturedValues TABLE (ID INT, Column1 NVARCHAR(100), Column2 NVARCHAR(100));

INSERT INTO YourTable (Column1, Column2)
-- Just like Santa Clause, but it's actually the OUTPUT clause bringing gifts of IDs.
OUTPUT INSERTED.ID, INSERTED.Column1, INSERTED.Column2 INTO @CapturedValues
VALUES ('Value1', 'Value2');

SELECT * FROM @CapturedValues;

Both methods retain relevant identity values even in an environment of concurrent database activity.

Use case: Dealing with triggers

Even though the OUTPUT clause offers all-inclusive results, SCOPE_IDENTITY() outshines it in scenarios where triggers are in play. If your table utilizes triggers, such as AFTER INSERT triggers, which can insert additional rows into another table with an identity column, then using SCOPE_IDENTITY() directly after your insert will ensure you get the correct id for your inserted row, defying any discrepancies caused by a trigger action.

Use case: Inserting multiple rows

In situations where multiple rows are being inserted simultaneously, relying solely on SCOPE_IDENTITY() won't cut it since it returns only the last generated ID. In such instances, the OUTPUT clause proves highly resourceful since it can output a list of IDs for all inserted rows handy for subsequent operations depending on these IDs.

Other methods for fetching auto-generated IDs

SQL Server isn't stingy when it comes to ways for fetching identity values. Besides OUTPUT and SCOPE_IDENTITY(), there are other options:

IDENT_CURRENT('TableName') - Returns the last identity for the specific table, irrespective of the scope or session fancy way of saying "I don't care, just give me the last identity for this table".
@@IDENTITY - Delivers the last identity value generated in any session or scope "the global chameleon of identity functions". However, note that this function might give misleading results in a concurrent scenario if triggers are involved.

Entity Framework tips

For those using Entity Framework (EF), some goodies are built-in to handle identity values post-inserts, masking much of the manual process shown above. Bear in mind to steer clear of redundant joins or complex query structures when dealing with raw SQL queries within EF, as it often handles these scenarios with simpler code and built-in methods.

Delving deeper: Non-integer IDs

Did you know SQL Server doesn't just restrict you to auto-incrementing integers? You can also have unique identifier types like GUIDs. If you're using GUIDs with default values generated by NEWID() or NEWSEQUENTIALID(), the OUTPUT clause can help fetch these values post-insertion.

The OUTPUT INTO catch

Be mindful when using the OUTPUT INTO clause. Double-check that your destination table is capable of receiving the conveyed data without violating any constraints. For instance, if your table variable has unique constraints, and you attempt to output a duplicate value, it will result in an error. Be your own validation officer before relying on the OUTPUT INTO statement.

Storing OUTPUT results for later

Won't always need the inserted IDs instantly? The OUTPUT clause's ability to store result sets in table variables or temporary tables ensures you can hold on to this data for later use during the session. Ideal for transactions or situations needing subsequent logic based on newly inserted rows.

explain-codes / Sql / Sql Server - Return value after INSERT

Linked

How to get the identity of an inserted row?



Sql Server: Is it possible to insert into two tables at the same time?



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



Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF



What does (1,1) mean in SQL?

