copy node

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Set Identity_Insert Departments On
GO
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 Departments As T
Cross Join( Select Max( id ) As Id From Departments ) As MaxId
Where T.Name = 'dep1'
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 Departments As Child
On Child.ParentId = Parent.OldId
)
Insert Departments( Id, ParentId, Name )
Select Id, ParentId, Name
From Data
GO
Set Identity_Insert Departments Off
GO