SQL - clone a record and its descendants

Question

I would like to be able to clone a record and its descendants in one table. An example of my table might be the following:

Table 1

id | parentid | name --------------------- 1 | 0 | 'Food' 2 | 1 | 'Taste' 3 | 1 | 'Price' 4 | 2 | 'Taste Requirements' 

The id column is the primary key and is automatically incremented. The entry "Food" (that is, where id = 1) contains two entries under it, "Taste" and "Price". The entry “Taste” has an entry called “Taste Requirements”. I would like to be able to clone the entry “Food” so that Table 1 looks like this:

Table 1

 id | parentid | name --------------------- 1 | 0 | 'Food' 2 | 1 | 'Taste' 3 | 1 | 'Price' 4 | 2 | 'Taste Requirements' 5 | 0 | 'Cookies' 6 | 5 | 'Taste' 7 | 5 | 'Price' 8 | 6 | 'Taste Requirements' 

(where "Cookies" is the name of the new category I want to create). I can select all descendants of "Food" using:

 with Table1_CTE( id, parentid, name ) as ( select t.id, t.parentid, t.name from Table1 t where t.id = 1 union all select t.id, t.parentid,t. name from Table1 t inner join Table1_CTE as tc on t.parentid = tc.id ) select id, parentid, name from Table1_CTE 

and I can only clone the entry "Food" (that is, where id = 1) using:

 insert into Table1 ( parentid, name ) select ( parentid, 'Cookies' ) from Table1 where id = 1 

but I'm having trouble trying to combine the two queries to clone the descendants of Food. In addition, I try to avoid using stored procedures, triggers, curosrs, etc. Is what I'm trying to make possible? I saw several examples on the Internet, but could not apply them to my requirements.

Answer 1

As Martin suggested, you need to enable IDENTITY_INSERT so that you can push your own values. You may also need to obtain a table lock to ensure that Max (Id) returns the correct value.

 If object_id('tempdb..#TestData') is not null Drop Table #TestData GO Create Table #TestData ( Id int not null identity(1,1) Primary Key , ParentId int not null , Name varchar(50) not null ) GO Set Identity_Insert #TestData On GO Insert #TestData( Id, ParentId, Name ) Values( 1,0,'Food' ) , ( 2,1,'Taste' ) , ( 3,1,'Price' ) , ( 4,2,'Taste Requirement' ); With Data As ( Select Cast(MaxId.Id + 1 As int) As Id , T.ParentId , 'Copy Of ' + T.name As Name , T.Id As OldId , 0 As OldParentId From #TestData As T Cross Join( Select Max( id ) As Id From #TestData ) As MaxId Where T.Name = 'Food' Union All Select Cast(Parent.id + Row_Number() Over( Order By Child.Id ) + 1 As int) , Parent.Id , 'Copy of ' + Child.Name , Child.Id , Child.ParentId From Data As Parent Join #TestData As Child On Child.ParentId = Parent.OldId ) Insert #TestData( Id, ParentId, Name ) Select Id, ParentId, Name From Data GO Set Identity_Insert #TestData Off GO 

results

  id |  parentid |  name
 - |  -------- |  -----------------
 1 |  0 |  Food
 2 |  1 |  Paste
 3 |  1 |  Price
 4 |  2 |  Paste requirements
 5 |  0 |  Copy of food
 7 |  5 |  Copy of paste
 8 |  5 |  Copy of Price
 9 |  7 |  Copy of Taste Requirement

Answer 2

Assuming your CTE selects the root record and all its descendants (it didn’t seem to me when I reproduced using your data above), you can clone all the selected records and insert like this:

 with Table1_CTE( id, parentid, name ) as ( select t.id, t.parentid, t.name from Table1 t where c.icategoryid = 1 union all select t.id, t.parentid,t. name from Table1 inner join Table1_CTE as tc on t.parentid = tc.id ) insert into dbo.testinsertheirarchy ( parentid, name ) select parentid, name from Table1_CTE