問題描述

我希望能夠在同一個表中克隆一條記錄及其后代.我的表的一個例子如下:

表 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 關閉走

結果

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

這篇關于SQL - 克隆記錄及其后代的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！