Скачать или смотреть How to Safely Retrieve the id of Newly Inserted Values in SQL

Learn the best practices for obtaining the `id` of auto-generated values after an `INSERT` in SQL. Avoid pitfalls with the wrong methods and optimize your database interactions!
---
This video is based on the question https://stackoverflow.com/q/45651/ asked by the user 'Niyaz' ( https://stackoverflow.com/u/184/ ) and on the answer https://stackoverflow.com/a/45667/ provided by the user 'SQLMenace' ( https://stackoverflow.com/u/740/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: SQL: How to get the id of values I just INSERTed?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 2.5' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 3.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Safely Retrieve the id of Newly Inserted Values in SQL

When working with databases in SQL, you often need to retrieve the unique identifier of a newly inserted record. This is particularly important when you're dealing with tables that have auto-generated values, such as primary keys. But how can you efficiently obtain this value right after the insertion? In this post, we'll uncover the best method to achieve this while avoiding common pitfalls.

The Challenge: Accessing New Record Identifiers

Imagine you have a table in your SQL database where one of the columns is set to auto-generate an identifier (id) upon insertion. After adding a record to this table, you may want to perform further operations that require the unique identifier you just created. The solution lies in which method to use for retrieving that identifier correctly.

Common Methods: The Misleading @@IDENTITY

A common method that many might consider is using @@IDENTITY. However, it comes with significant drawbacks since it is not scope-safe. Here’s what you need to know:

What is @@IDENTITY?: It returns the last-generated identity value for the current session, regardless of the table it comes from.

The Risk: If there are triggers on the table during the insertion, or if inserts occur into multiple tables, using @@IDENTITY can lead to unexpected behavior. You might retrieve an id from another table entirely!

The Recommended Approach: Using SCOPE_IDENTITY()

To safely retrieve the identifier of the record you just inserted, SCOPE_IDENTITY() is the method you should use. Here’s why it’s recommended:

Scope-Safe: Unlike @@IDENTITY, SCOPE_IDENTITY() returns the last identity value generated for a specific table within the same scope. This means that even if there are triggers in place, it will not return an identifier from those tables.

Exact and Reliable: It ensures that the value you retrieve is exactly the one associated with your latest INSERT operation.

How to Use SCOPE_IDENTITY()

Here’s a quick breakdown of how to use SCOPE_IDENTITY() effectively:

Perform the Insert: First, insert your record into the table.

[[See Video to Reveal this Text or Code Snippet]]

Retrieve the New ID: Immediately after the insert statement, call SCOPE_IDENTITY() to get the new identifier.

[[See Video to Reveal this Text or Code Snippet]]

Use the New ID: Now, you can use @NewID for any further operations you need to perform with the newly inserted record.

Conclusion

Retrieving the id of newly inserted values in SQL can be straightforward if you use the correct method. By opting for SCOPE_IDENTITY(), you ensure that the identifier you work with is safe and relevant to your current transaction, avoiding potential issues that could arise with less reliable methods like @@IDENTITY. Always keep this in mind to maintain data integrity and streamline your database interactions. Happy coding!