--*/
--创建数据测试环境
create table #tb(id int identity(1,1),parentid int,name varchar(10))
insert into #tb
select 0,'中国'
union all select 1,'广东'
union all select 1,'广西'
union all select 1,'四川'
union all select 2,'广州'
union all select 2,'佛山'
union all select 2,'东莞'
union all select 5,'越秀区'
union all select 5,'海珠区'
union all select 5,'芳村'
union all select 6,'禅城区'
union all select 6,'南海区'
union all select 11,'石湾'
go
--创建复制的存储过程
create proc p_copy
@s_id int, --复制该项下的所有子项
@d_id int, --复制到此项下
@new_id int --新增加项的开始编号
as
declare @nid int,@oid int,@name varchar(10)
select id,name into #temp from #tb where parentid=@s_id and id<@new_id
while exists(select 1 from #temp)
begin
select @oid=id,@name=name from #temp
insert into #tb values(@d_id,@name)
set @nid=@@identity
exec p_copy @oid,@nid,@new_id
delete from #temp where id=@oid
end
go
--创建批量复制的存储过程
create proc p_copystr
@s_id varchar(8000) --要复制项的列表,用逗号分隔
as
declare @nid int,@oid int,@name varchar(10)
set @s_id=','+@s_id+','
select id,name into #temp from #tb
where charindex(','+cast(id as varchar)+',', @s_id)>0
while exists(select 1 from #temp)
begin
select @oid=id,@name=name from #temp
insert into #tb values(@oid,@name)
set @nid=@@identity
print @oid
print @nid
exec p_copy @oid,@nid,@nid
delete from #temp where id=@oid
end
go
--测试
exec p_copystr '5,6'
--显示处理结果
select * from #tb
go
--删除数据测试环境
drop proc p_copystr,p_copy
drop table #tb
--*/
--创建数据测试环境
create table #tb(id int identity(1,1),parentid int,name varchar(10))
insert into #tb
select 0,'中国'
union all select 1,'广东'
union all select 1,'广西'
union all select 1,'四川'
union all select 2,'广州'
union all select 2,'佛山'
union all select 2,'东莞'
union all select 5,'越秀区'
union all select 5,'海珠区'
union all select 5,'芳村'
union all select 6,'禅城区'
union all select 6,'南海区'
union all select 11,'石湾'
go
--创建复制的存储过程
create proc p_copy
@s_id int, --复制该项下的所有子项
@d_id int --复制到此项下
as
declare @nid int,@oid int,@name varchar(10)
select id,name into #temp from #tb where parentid=@s_id
while exists(select 1 from #temp)
begin
select @oid=id,@name=name from #temp
insert into #tb values(@d_id,@name)
set @nid=@@identity
exec p_copy @oid,@nid
delete from #temp where id=@oid
end
go