
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.

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.
Comments