How to output inserted and source IDs when cloning SQL Server table data

If you write T-SQL scripts for SQL Server or Azure SQL databases, you will likely come across the requirement to ‘clone’ existing data at some point along the way. It’s usually quite straightforward to accomplish a clone operation when copying data from a single table to another table, but how about when you need to copy data from a parent table along with related data from one or more child tables? This is where it can become a little bit tricky, as you’ll need to somehow map the IDs that are used to establish the parent-child relationships.

You may be already aware of the T-SQL OUTPUT clause, which can be used to output the values of inserted, updated, or deleted rows when modifying data. This is very useful as it allows us to capture the inserted ID values to use when performing subsequent inserts. However, it doesn’t solve the problem of capturing the corresponding ID value from the source row so that we can map IDs across child tables.

In this article, I will demonstrate how you can output both the inserted and source IDs when cloning data in a SQL Server or Azure SQL database.

Setup

To enable an effective demonstration, let’s start by setting up a database that we can use to test.

If you want to follow along, I recommend that you install the latest version of SQL Server or LocalDB (usually you will already have access to LocalDB if you have Visual Studio installed) and the latest version of SQL Server Management Studio (SSMS) or Azure Data Studio for interacting with the SQL Server database.

Within the context of your preferred database management platform, you can run the following SQL script to create a demo database, tables, and sample data.

-- Create demo database.
CREATE DATABASE CloneDemo;
GO
 
USE CloneDemo;
GO
 
-- Transactions Table (Parent).
CREATE TABLE dbo.Transactions
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CreatedOn DATETIME2 NOT NULL
);
 
-- Transaction_LineItems Table (1-Many with Transactions).
CREATE TABLE dbo.Transaction_LineItems
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TransactionId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    FOREIGN KEY (TransactionId) REFERENCES dbo.Transactions(Id) ON DELETE CASCADE
);
 
-- Transaction_LineItem_Product Table (1-1 with Transaction_LineItems).
CREATE TABLE dbo.Transaction_LineItem_Product
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TransactionId INT NOT NULL,
    TransactionLineItemId INT NOT NULL UNIQUE,
    Code NVARCHAR(50) NOT NULL,
    Title NVARCHAR(100) NOT NULL,
    FOREIGN KEY (TransactionLineItemId) REFERENCES dbo.Transaction_LineItems(Id) ON DELETE CASCADE,
    FOREIGN KEY (TransactionId) REFERENCES dbo.Transactions(Id)
);
 
-- Insert Sample Transactions (5 Transactions).
DECLARE @TransactionId INT;
DECLARE @TransactionLineItemId INT;
 
-- Transaction 1.
INSERT INTO dbo.Transactions (CreatedOn) VALUES ('2025-01-01 12:53:27');
SET @TransactionId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 353.99);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'GN-2432435', 'Super Yacht');
 
-- Transaction 2.
INSERT INTO dbo.Transactions (CreatedOn) VALUES ('2025-01-12 09:34:53');
SET @TransactionId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 150.75);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'BK-857392', 'Luxury Watch');
 
-- Transaction 3.
INSERT INTO dbo.Transactions (CreatedOn) VALUES ('2025-02-02 14:15:19');
SET @TransactionId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 245.50);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'EL-934234', 'Smartphone');
 
-- Transaction 4 (Multiple Line Items).
INSERT INTO dbo.Transactions (CreatedOn) VALUES ('2025-02-10 18:45:00');
SET @TransactionId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 500.00);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'TV-442389', '4K OLED TV');
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 99.99);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'SP-923487', 'Bluetooth Speaker');
 
-- Transaction 5.
INSERT INTO dbo.Transactions (CreatedOn) VALUES ('2025-03-01 10:20:33');
SET @TransactionId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItems (TransactionId, Amount) VALUES (@TransactionId, 799.00);
SET @TransactionLineItemId = SCOPE_IDENTITY();
 
INSERT INTO dbo.Transaction_LineItem_Product (TransactionId, TransactionLineItemId, Code, Title) 
VALUES (@TransactionId, @TransactionLineItemId, 'LT-192834', 'Gaming Laptop');

Note that this schema is purely for demonstration purposes and is not a specific recommendation on how you should structure your database tables and relationships.

The above script sets up a ‘Transactions’ parent table with an auto-incrementing ‘Id’ column.

A ‘Transaction_LineItems’ child table table is then created; there could be one or more Transaction Line Items per Transaction.

The ‘Transaction_LineItems’ table has its own child table named ‘Transaction_LineItem_Product’ that separates out the product details. There is one product record per line item.

After creating the tables, the script then inserts some sample data.

At this point, we are ready to consider what approach we could take to clone existing transaction parent records and the associated child records.

The output problem

Near the beginning of this article, I mentioned that when inserting data, the OUTPUT clause provides us with a way of capturing values that have been inserted, but it doesn’t allow us to correlate these values with the source records.

To make this a bit clearer, consider the following SQL.

DECLARE @TransactionMappings TABLE (SourceId INT, CloneId INT);
 
INSERT INTO dbo.Transactions (CreatedOn)
OUTPUT src.Id AS SourceId, INSERTED.Id AS CloneId
INTO @TransactionMappings (SourceId, CloneId)
SELECT src.CreatedOn
FROM dbo.Transactions src;

The above code creates a mapping table to capture the ID of the source record we are cloning and the corresponding ID of the cloned record, to help us when performing subsequent inserts for child tables.

The code then attempts to insert existing data from the ‘Transactions’ table, effectively cloning all existing records. The OUTPUT clause has been configured to output the ID from each source transaction record, along with the inserted ID for the cloned record.

Unfortunately, the above code is not valid. If we attempt to run it we will receive the following error message.

The multi-part identifier “src.Id” could not be bound.

The OUTPUT clause is limited when inserting data this way, as it only allows us to capture the inserted ID values.

Solving the problem

To solve the output problem, we can use a neat trick by employing the MERGE statement.

Normally, the MERGE statement is used when you want to handle a combination of insert, update, and delete operations as part of a single SQL command. However, we can also use it to get around the output limitation.

Take a look at the following code to see how it works.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
 
-- Transactions.
DECLARE @TransactionMappings TABLE (SourceId INT, CloneId INT);
 
MERGE INTO dbo.Transactions
USING (
    SELECT *
    FROM dbo.Transactions
    WHERE CreatedOn >= '2025-02-10'
) AS src
ON 1 = 0 -- Non-matching condition to force INSERT.
WHEN NOT MATCHED THEN
    INSERT (
	CreatedOn
    )
    VALUES (
	CreatedOn
    )
OUTPUT
    src.Id AS SourceId,
    INSERTED.Id AS CloneId
INTO @TransactionMappings (SourceId, CloneId);
 
-- Transaction Line Items.
DECLARE @TransactionLineItemMappings TABLE (SourceId INT, CloneId INT);
 
MERGE INTO dbo.Transaction_LineItems
USING (
    SELECT
	tl.Id,
	tm.CloneId AS TransactionId,
	tl.Amount
    FROM dbo.Transaction_LineItems tl
    INNER JOIN @TransactionMappings tm ON tl.TransactionId = tm.SourceId
) AS src
ON 1 = 0 -- Non-matching condition to force INSERT.
WHEN NOT MATCHED THEN
    INSERT (
	TransactionId,
	Amount
    )
    VALUES (
	TransactionId,
	Amount
    )
OUTPUT
    src.Id AS SourceId,
    INSERTED.Id AS CloneId
INTO @TransactionLineItemMappings (SourceId, CloneId);

-- Transaction Line Item Product.
INSERT INTO dbo.Transaction_LineItem_Product (
    TransactionId,
    TransactionLineItemId,
    Code,
    Title
)
SELECT
    tm.CloneId AS TransactionId,
    tlm.CloneId AS TransactionLineItemId,
    tlip.Code,
    tlip.Title
FROM dbo.Transaction_LineItem_Product tlip
INNER JOIN @TransactionLineItemMappings tlm ON tlip.TransactionLineItemId = tlm.SourceId
INNER JOIN dbo.Transaction_LineItems tli ON tlm.SourceId = tli.Id
INNER JOIN @TransactionMappings tm ON tli.TransactionId = tm.SourceId;
 
COMMIT TRANSACTION;

The first thing to say about the above code is that everything is wrapped in a SQL transaction so that the cloned data will only persist if all the insert statements succeed.

As per the example from the previous section, a mapping table is created to hold the source and clone ID values.

Next, the MERGE statement is used to merge/insert data into the ‘Transactions’ table for all transactions created on or after ‘2025-02-10’. The data to use as the source for the insert is selected within the USING block and can contain whatever joins and conditions you require.

A key part of the code is the condition of ‘1 = 0’ which will never match; this forces the MERGE statement to always perform the insert operation.

After this, the data is inserted and any column values selected within the USING block can be accessed at this point.

The OUTPUT clause is then configured and because we are using it in combination with the MERGE statement, we can access the source values that we normally wouldn’t be able to and insert both the source and clone ID values into the mapping table, pretty neat!

The script follows a similar pattern for the ‘Transaction_LineItems’. Notice the join back to ‘@TransactionMappings’ table variable, matching on the ‘SourceId’ column. This allows us to select data from the source transaction record while using the corresponding ‘CloneId’ column for the ‘TransactionId’ so that we can link things together.

For the ‘Transaction_LineItem_Product’ table, a normal INSERT statement is used, as there are no further child tables to map to. There are just a few more joins needed to map all of the ID values correctly.

Reviewing the results

To help visualise the script’s outcome, please see the screenshot from SQL Server Management Studio below.

SQL Server Management Studio Results - Before and After
SQL Server Management Studio Results – Before and After

The left-hand side of the screen displays the contents of the transaction tables before running the clone query and the right-hand side displays the results after executing the query.

As you can see, the data has been successfully cloned, while linking everything together according to the newly inserted ID values across the tables.

Conclusion

The approach outlined in this article can be used regardless of how complex your schema is. As long as you set up a mapping table at each level and use the MERGE statement everywhere except the ‘leaf’ level, you will be able to join the data back up through child and parent tables.

For more complex scenarios, you also have the option to include more source and clone columns in your mapping table to reduce the number of joins required at lower levels e.g. you could have ‘SourceId’ and ‘SourceNumber’ columns along with ‘CloneId’ and ‘CloneNumber’ columns depending on your schema. All you need to do is select the additional columns as part of the MERGE statement and include them in the OUTPUT clause.

I trust that you will find this method of cloning data useful in one of your future projects.


I hope you enjoyed this post! Comments are always welcome and I respond to all questions.

If you like my content and it helped you out, please check out the button below 🙂

Comments