Clone records with descendants in sql server

Question

Below is my table.

CREATE TABLE [dbo].[CCMaster](
[CCId] [int] IDENTITY(1,1) NOT NULL,
[GId] [int] NULL,
[BKId] [int] NULL,
[Class] [varchar](100) NULL,
[ParentId] [int] NULL,)

SET IDENTITY_INSERT CCMaster ON

insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(1,33,162,'CORPORATE',NULL)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(10,33,162,'Call center related',4)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(11,33,162,'Channel related',2)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(12,33,162,'Advertisement',6)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(13,33,162,'Brand Ambassador',6)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(14,33,162,'OTAF/TVP (New Activation)',11)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(15,33,162,'Service Barred',7)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(16,33,162,'Call center behaviour',10)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(17,33,162,'Store Personnel behavior',8)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(2,33,162,'DTH',NULL)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(3,33,162,'2G',NULL)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(4,33,162,'3GS',NULL)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(5,33,162,'Broadband',NULL)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(6,33,162,'Brand',1)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(7,33,162,'Barring-related',3)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(8,33,162,'Behaviour related',4)
insert into CCMaster([CCId],[GID],[BKID],[Class],[ParentId]) values(9,33,162,'Call center related',3)

SET IDENTITY_INSERT CCMaster OFF

There are thousands of records in this table. I want to clone only the records where GID = 33 and BKID = 162. The parent of the child must match accordingly to the auto generated id of the respective parent. I tried this query but it din't work for me.

I tried using a cursor by first inserting the parents and then trying to query for the child but that too din't work out. Any help would be appreciated.

Do you want to copy the data into the same table with different values for the ID columns? Or do you want to copy it into a new table? Can you share an example CREATE TABLE statement with us? — Iain Samuel McLean Elder, Jul 03 '14 at 14:00
I want to copy it into the same table with different values for id columns. I will update my answer with the table structure — Hitesh, Jul 03 '14 at 14:02
And could you clarify this statement"parent of the child must match accordingly to the auto generated id of the respective parent"? Do you mean for example that a clone of CCID=6 above would then get a parentID of 6? — Kevin Roche, Jul 03 '14 at 14:05
Can you post the sample data as text? Insert statements are ideal, tab-seperated values are fine too (just copy and paste from the results panel). — Iain Samuel McLean Elder, Jul 03 '14 at 14:08
Do you want the parent ID to refer to the NEW records or the OLD ones? If the record which is currently CCId #7 will become #3087, then does #15's copy's parent become #3087 or stay as #7? — Ruslan, Jul 03 '14 at 15:09
I want the cloned child record to have the parent id of the newly generated record, not from the record from which it has been cloned. I have added insert statements in my question for sample data — Hitesh, Jul 04 '14 at 05:49

score 2 · Accepted Answer · edited May 23 '17 at 12:01

2

Use a recursive CTE to get the rows you want to insert.

To figure out what new value is to be used as ParentId you can use merge with output to get a mapping between the old CCId and the new CCId. Capture the output from the merge to table variable and after the merge you can update ParentId from the table variable.

declare @T table
(
  OldCCId int,
  OldParentId int,
  NewCCId int
);

with C as 
(
  select M.*
  from dbo.CCMaster as M
  where ParentId is null and
        GId = 33 and
        BKId = 162
  union all
  select M.*
  from dbo.CCMaster as M
    inner join C
      on C.CCId = M.ParentId
)
merge into dbo.CCMaster as M
using C
on 1 = 0
when not matched then
  insert(GID, BKID, Class)
    values (C.GID, C.BKID, C.Class)
output C.CCId,
       C.ParentId,
       inserted.CCId
  into @T;

update M
set ParentId = T2.NewCCId
from CCMaster as M
  inner join @T as T1
    on M.CCId = T1.NewCCId
  inner join @T as T2
    on T1.OldParentId = T2.OldCCId;

SQL Fiddle

edited May 23 '17 at 12:01

Community

1
1

answered Jul 04 '14 at 06:04

Mikael Eriksson

136,425
22
210
281

Thanks Mikael, works perfectly. I had never known the use of merge, this has encouraged me to start using it. – Hitesh Jul 04 '14 at 07:02
@HiteshSalian Great. There are some reported issues with merge that you need to be aware of. They might apply to you or they might not but it does not hurt to know about them. [Use Caution with SQL Server's MERGE Statement](http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) – Mikael Eriksson Jul 04 '14 at 07:07
Oh thanks a lot for that Mikael. Will keep in mind about this if I get affected by it. – Hitesh Jul 04 '14 at 07:19

Clone records with descendants in sql server

1 Answers1