Click here to Skip to main content
Click here to Skip to main content

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

, 2 Mar 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Replicate/Clone/Duplicate selected data in a Table using merge statment

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Syed Moula Ali
Software Developer
India India
I Started my Programming career with C#. Later got a chance to develop Windows Form applications using C#. Now using C#, WCF, TFS, Entity Framework & Silverlight 5.0 to develop an application, for tagging finance data and generate XBRL files.
 
My interests involves Azure, ASP.NET SPA, SQL Server 2012, .Net 4.5.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 4 PinmemberNeenaM6-Mar-13 8:17 
GeneralRe: My vote of 4 PinmemberSyed Moula Ali6-Mar-13 15:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 2 Mar 2013
Article Copyright 2013 by Syed Moula Ali
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid