正如Martin所建议的，您需要启用
IDENTITY_INSERT以便可以推送自己的标识值。您可能还需要获取表锁，以确保Max（Id）返回正确的值。
If object_id('tempdb..#TestData') is not null Drop Table #TestDataGOCreate Table #TestData ( Id int not null identity(1,1) Primary Key , ParentId int not null , Name varchar(50) not null )GOSet Identity_Insert #TestData onGO 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, NameFrom DataGOSet Identity_Insert #TestData OffGO结果
id | parentid | name-- | -------- | -----------------1 | 0 | Food2 | 1 | Taste3 | 1 | Price4 | 2 | Taste Requirement5 | 0 | Copy Of Food7 | 5 | Copy of Taste8 | 5 | Copy of Price9 | 7 | Copy of Taste Requirement
