Скачать или смотреть Efficiently Use OUTPUT to Insert into Two Tables Concurrently in SQL Server

Learn how to populate two tables using a single `INSERT` command in SQL Server with the help of the `OUTPUT` clause.
---
This video is based on the question https://stackoverflow.com/q/76657073/ asked by the user 'SkyeBoniwell' ( https://stackoverflow.com/u/880874/ ) and on the answer https://stackoverflow.com/a/76657841/ provided by the user 'Patrick Hurst' ( https://stackoverflow.com/u/18522514/ ) 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, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How can I do an insert for two tables at the same time using SCOPE_IDENTITY()?

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 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.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 Insert into Two Tables at the Same Time Using SCOPE_IDENTITY()

The Challenge: Inserting Data into Two Tables

When working with SQL Server, it is a common scenario to require data to be inserted into multiple tables simultaneously, especially when dealing with related data. For instance, you may need to populate a Cameras table with various camera details and a corresponding CameraLenses table that includes the lenses linked to each camera.

A common problem arises when you need to retrieve the automatically generated identity value from the first table and use it in the second table. Trying to do this with SCOPE_IDENTITY() often leads to errors, as you've likely experienced.

The Pitfall of SCOPE_IDENTITY()

Using SCOPE_IDENTITY() to retrieve the last identity value generated in the current session can seem like an ideal solution. However, it has limitations, and it can introduce complexity and errors in your queries. This is particularly true when you are inserting multiple rows at once, where the identity can change between insertions. So instead, there's a more efficient way to achieve your goals.

The Solution: Using the OUTPUT Clause

Instead of using SCOPE_IDENTITY(), the OUTPUT clause provides a streamlined way to capture inserted data and link it between tables. Let's break down the process into manageable steps:

Step 1: Prepare Your Temporary Tables

First, you need to create your tables. For the sake of this example, let's assume you’re working with temporary tables for demonstration purposes.

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

Step 2: Declare an Outputs Table

You will declare a table variable to capture the results of the inserted rows from the first table. This allows you to reference the newly created identity values later.

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

Step 3: Insert into the First Table and Capture Outputs

When you insert into the # Cameras table, you can use the OUTPUT clause to capture the inserted identity along with additional identifying information.

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

Step 4: Insert into the Second Table Using JOIN

Next, you will perform the insert into the # CameraLenses table. Here, you will join the source data on the product code to link back to the identity values captured in the @ Outputs table.

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

Step 5: Review the Results

Finally, you can verify the data entered into both tables by performing a SELECT query that joins the two tables.

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

Conclusion

Using the OUTPUT clause is a powerful and efficient way to insert data into multiple related tables in SQL Server. This approach eliminates the issues presented by SCOPE_IDENTITY() while providing a straightforward mechanism to streamline data inserts. By breaking down the process into clear steps, you can insert rows into multiple tables seamlessly without encountering the syntax errors commonly associated with SCOPE_IDENTITY().

Now, you're set to tackle your SQL Server challenges with confidence! Happy coding!