How to Efficiently Copy Data Between Tables in SQL Server Using INSERT with OUTPUT

Learn how to solve SQL Server's limitation with INSERT OUTPUT queries and effectively map primary keys when copying data between tables.
---

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: Return a source table column in an INSERT .. OUTPUT .. SELECT query

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Efficiently Copy Data Between Tables in SQL Server Using INSERT with OUTPUT

When working with SQL Server, transferring data between tables can often present its own challenges, especially when it involves maintaining relationships between primary and foreign keys. One common scenario occurs when you want to copy data from one table to another while also creating a mapping of old and new primary keys. This post will address the problem and provide a solution that involves leveraging the MERGE statement instead of a simple INSERT ... OUTPUT query.

The Problem: Copying Data and Mapping Keys

Imagine you have two tables, T1 and T2, along with child tables C1 and C2. The objective is to copy data from T1 to T2 and maintain a mapping between the old IDs from T1 and the new IDs generated in T2. Using a straightforward INSERT ... OUTPUT approach can lead to complications, as you may run into errors related to binding identifiers from the source table, like the one shown below:

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

As highlighted in the error message, INSERT ... OUTPUT can only output columns from the target table that is being modified. This limitation can be a hurdle for developers looking to easily map the generated IDs against their source data.

The Solution: Using MERGE for Data Transfer and Mapping

To achieve both tasks — inserting new records while capturing data from the source table — you can use the MERGE statement. The MERGE statement allows you to both insert new records and retrieve values from the source table in a single query, addressing the issues encountered with INSERT ... OUTPUT. Here's how to set it up step by step.

Step 1: Setting Up Your Key Mapping Table

First, create a temporary table to hold your old and new ID mappings.

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

Step 2: Perform the Merge Operation

Next, use the MERGE statement to insert data into T2 from T1. In this case, you'll focus on the X, Y, and Z columns, and you can also output the newly inserted ID and the corresponding ID from T1 into the @ keyMapping table.

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

Conclusion

Utilizing the MERGE statement provides a powerful way to handle the data transfer and relationships between tables in SQL Server. By effectively mapping old and new primary keys, developers can ensure the integrity of their data model while simplifying the copying process. This method allows for greater flexibility and eliminates the binding issues seen with the traditional INSERT ... OUTPUT approach.

Should you encounter similar data management challenges, consider this method as a clean and efficient solution. Happy querying!