To Replicate/Clone/Duplicate, selected data in SQL Server 2012, 2008

Introduction

In this article, we will discuss how to duplicate/clone/replicate data using merge statement in SQL Server.

Background

We always come across situations where we need to clone data in table. We need to keep track of inserted rows data and update data using cursor or while loop. Using merge statement, it is quite easy and the performance is quite good when compared with while/cursor.

Using the Code

Let's create a sample table and insert some sample data:

CREATE TABLE [Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [PersonID] [int] NOT NULL,
    [StoreID] [int] NOT NULL,
 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)
) ON [PRIMARY]

CREATE TABLE [Person](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [VARCHAR](20) NOT NULL,
    [Mobile] [INT] NOT NULL,
 CONSTRAINT [PK_Person_PersonID] PRIMARY KEY CLUSTERED 
(
    [PersonID] ASC
)
) ON [PRIMARY]

CREATE TABLE [Store](
    [StoreID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [VARCHAR](20) NOT NULL,
    [Country] [VARCHAR](20) NOT NULL,
 CONSTRAINT [PK_Store_StoreID] PRIMARY KEY CLUSTERED 
(
    [StoreID] ASC
)
) ON [PRIMARY]       

Inserting sample data.

--Insert Sample data
insert into Customer values(1,1)
insert into Customer values(2,2)
insert into Customer values(3,3)


insert into Person values('ABC',123)
insert into Person values('CDE',456)
insert into Person values('EFG',789)


insert into Store values('Walmart','US')
insert into Store values('Bigbazar','India') 
insert into Store values('Goodies','UK')   

Quick select statement to see data in those tables before replicating/cloning/duplicating:

Now, I want to replicate the data in customer table for customer id 2 and 3. Before doing that, we need to replicate the data in person, store table for those corresponding ids.

Let us use the merge statement to replicate data in Person table, we will use table variable to capture the old person id as well as it new generated id.

--Table variable 
declare @PersonLookup table
    (
        OldPersonId int,
        NewPersonId int 
    )
    
--Merge statement to duplicate Person data, 1=0 condition is always false so not matched condition, 
--inserted alias has new created rows data      
merge Person as [t]
    using 
    (
        select PersonID,
                Name,
                Mobile
        from Person where PersonID in (select PersonID from customer c where c.customerID in (2,3) )
    )as [s] (
                PersonID,
                Name,
                Mobile
            )
    on 1=0
    when not matched then
        insert
        (
            Name,
            Mobile
        )
        values
        (
            [s].Name,
            [s].Mobile
        ) 
output [s].PersonID,inserted.PersonID into @PersonLookup;  

Let us replicate the data in store table for those corresponding ids in customer table.

declare @StoreLookup table
    (
        OldStoreId int,
        NewStoreId int 
    )
    
--Merge statement to duplicate Store data, 1=0 condition is always false so not matched condition, 
--inserted alias has new created rows data          
merge Store as [t]
    using 
    (
        select StoreID,
                Name,
                Country
        from Store where StoreID in (select StoreID from customer c where c.customerID in (2,3) )
    )as [s] (
                StoreID,
                Name,
                Country
            )
    on 1=0
    when not matched then
        insert
        (
            Name,
            Country
        )
        values
        (
            [s].Name,
            [s].Country
        )
output [s].StoreID,inserted.StoreID into @StoreLookup; 

Replicating the data in the customer table, we need to track only the new ids, in case you require to capture the old id, you can do that, have a look at the above code.

Once the data is replicated for the customer table (Note: we are replicating data only for cutomerid 2 and 3) we can go ahead and update the data accordingly as we have tracked the corresponding old and new id.

--We just need newly created rows to update data
declare @CustomerLookup table
    (
    NewCustomerId int 
    )

--Merge statement to duplicate Customer data, 1=0 condition is always false so not matched condition, 
--inserted alias has new created rows data              
merge Customer as [t]
    using 
    (
        select CustomerID,
                PersonID,
                StoreID
        from Customer c where c.CustomerID in (2,3)
    )as [s] (
                CustomerID,
                PersonID,
                StoreID
            )
    on 1=0
    when not matched then
        insert
        (
                PersonID,
                StoreID
        )
        values
        (
        [s].PersonID,
        [s].StoreID
        )
output inserted.CustomerID into @CustomerLookup;

--Finally we need to update the data for personid, storeid.
update c set c.PersonID=p.NewPersonId from customer c inner join @PersonLookup p on _
p.OldPersonId=c.PersonID where c.CustomerID in (select NewCustomerId from @CustomerLookup) 
update c set c.StoreID=s.NewStoreId from customer c inner join @StoreLookup s on _
s.OldStoreId=c.StoreID where c.CustomerID in (select NewCustomerId from @CustomerLookup)  

Finally, once you run the above DB script, you can see the data is replicated. Below are the results:

Points of Interest

The 1=0 condition that we have used in the merge condition is always false, we will insert data whenever the condition is false. We can also use 2=3 for example.

Using merge, it is very easy to track old and new data.

History

• 02 March 2013: First version