SQL - 克隆记录及其后代

我希望能够在同一个表中克隆一条记录及其后代.我的表的一个例子如下:

表 1

id |父母 |姓名---------------------1 |0 |'食物'2 |1 |'品尝'3 |1 |'价钱'4 |2 |'口味要求'

id"列是主键和自动增量.'Food' 记录(即其中 id = 1)在它下面有两个记录，分别称为 'Taste' 和 'Price'.品味"记录下面有一个名为品味要求"的记录.我希望能够克隆食物"记录，以便 Table1 如下所示:

表 1

id |父母 |姓名---------------------1 |0 |'食物'2 |1 |'品尝'3 |1 |'价钱'4 |2 |'口味要求'5 |0 |'饼干'6 |5 |'品尝'7 |5 |'价钱'8 |6 |'口味要求'

(其中Cookies"是我要创建的新类别的名称).我可以使用以下方法选择食物"的所有后代:

with Table1_CTE( id, parentid, name )作为(从 Table1 t 中选择 t.id、t.parentid、t.name其中 t.id = 1联合所有选择 t.id、t.parentid、t.表 1 中的名称内连接 Table1_CTE 作为 tc在 t.parentid = tc.id)从 Table1_CTE 中选择 id、parentid、name

并且我能够使用以下方法克隆食物"记录(即其中 id = 1):

insert into Table1 ( parentid, name )选择( parentid, 'Cookies' )来自表 1，其中 id = 1

但是我在尝试组合两个查询以克隆Food"的后代时遇到问题.此外，我试图避免使用存储过程、触发器、curosrs 等.我正在尝试做的可能吗?我在网上看到了一些示例，但无法将它们应用到我的要求中.

解决方案

正如 Martin 所建议的，您需要启用 IDENTITY_INSERT 以便您可以推送您自己的身份值.您可能还需要获取表锁以确保 Max( Id ) 返回正确的值.

如果 object_id('tempdb..#TestData') 不为空删除表#TestData走创建表#TestData(Id int not null identity(1,1) 主键, ParentId int 不为空, 名称 varchar(50) 不为空)走设置 Identity_Insert #TestData On走插入 #TestData( Id, ParentId, Name )值( 1,0,'食物' ), ( 2,1,'味道' ), ( 3,1,'价格' ), ( 4,2,'口味要求' );使用数据作为(选择 Cast(MaxId.Id + 1 As int) 作为 Id, T.ParentId, 'Copy Of ' + T.name 作为名字, T.Id 作为 OldId, 0 作为 OldParentId从#TestData As TCross Join( Select Max( id ) As Id From #TestData ) As MaxId其中 T.Name = '食物'联合所有选择 Cast(Parent.id + Row_Number() Over( Order By Child.Id ) + 1 As int), Parent.Id, '副本 ' + Child.Name, Child.Id, Child.ParentId从数据作为父加入#TestData 作为孩子Child.ParentId = Parent.OldId)插入 #TestData( Id, ParentId, Name )选择 ID、ParentId、名称从数据走设置 Identity_Insert #TestData 关闭走

结果

<前>身份证 |父母 |姓名-- |-------- |-----------------1 |0 |食物2 |1 |品尝3 |1 |价钱4 |2 |口味要求5 |0 |食物副本7 |5 |味道的副本8 |5 |价格副本9 |7 |口味要求复印件

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

Table1

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

The "id" column is the primary key and auto-increments. The 'Food' record (i.e. where id = 1) has two records underneath it called 'Taste' and 'Price'. The 'Taste' record has a record underneath it called 'Taste Requirements'. I would like to be able to clone the 'Food' record so that Table1 would look like the following:

Table1

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 that I want to create). I am able to select all the 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 am able to clone just the 'Food' record (i.e. where id = 1) using:

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

but I am having problems trying to combine the two queries to clone the descendants of 'Food'. Also, I am trying to avoid using stored procedures, triggers, curosrs, etc. Is what I am trying to do possible? I have seen some examples on the web but have been unable to apply them to my requirements.

解决方案

As Martin suggested, you need to enable IDENTITY_INSERT so that you can push your own identity values. You may also need to acquire 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        | Taste
3  | 1        | Price
4  | 2        | Taste Requirement
5  | 0        | Copy Of Food
7  | 5        | Copy of Taste
8  | 5        | Copy of Price
9  | 7        | Copy of Taste Requirement