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






4.50/5 (2 votes)
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