34,590
社区成员
发帖
与我相关
我的任务
分享
SET IDENTITY_INSERT tabname on;
go
insert into b(id,name,pid)
select id,name,pid from a
go
SET IDENTITY_INSERT tabname off;
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID] int,[Name] nvarchar(23),[PID] int)
Insert #A
select 1,N'江苏省',0 union all
select 2,N'南京市',1 union all
select 3,N'浙江省',0 union all
select 4,N'杭州市',3
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int,[Name] nvarchar(23),[PID] int)
Insert #B
select 1,N'安徽省',0 union all
select 2,N'合肥市',1 union all
select 3,N'山东省',0 union all
select 4,N'济南市',3
Go
insert into #B
select id+ref_id,name,case when pid=0 then 0 else pid+ref_id end
from #A
join (select max(id) as ref_id from #b) as B on 1=1
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID] int,[Name] nvarchar(23),[PID] int)
Insert #A
select 1,N'江苏省',0 union all
select 2,N'南京市',1 union all
select 3,N'浙江省',0 union all
select 4,N'杭州市',3
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int,[Name] nvarchar(23),[PID] int)
Insert #B
select 1,N'安徽省',0 union all
select 2,N'合肥市',1 union all
select 3,N'山东省',0 union all
select 4,N'济南市',3
Go
--测试数据结束
UPDATE #A SET ID=(SELECT MAX(ID)FROM #B)+#A.ID
UPDATE #A SET PID =(SELECT MAX(ID)FROM #B)+#A.PID WHERE PID<>0
INSERT INTO #B
SELECT * FROM #A
SELECT * FROM #B
INSERT INTO B
( Name, PID )
SELECT Name ,
PID
FROM A
这个和递归什么关系?读取的时候按照递归读取就可以了吧