操作数据库表结果如下 表1 ID PID 名称 编号, 1 a 00001 2 b 00002 3 c 00003 4 d 00004 5 e 00013 表2 PID 名称 编号, 1 a 00023 2 b 00024 3 c 00032 4 d 00033 5 e 00025要得到结果是 表1 ID PID 名称 编号, 1 6 a 00001 2 7 b 00002 3 8 c 00003 4 9 d 00004 5 10 e 00013 再把表1数据复制到表2中,表2是PID字段值是不允许重复(是唯一)
试试可不可以 CREATE proc bb asdeclare @id int ,@maxpid int set @id=0 set @maxpid=0declare pid cursor for select id from a where pid is null order by id for read only open pid fetch next from pid into @id while @@fetch_status = 0 begin select @maxpid=isnull(max(pid),0)+1 from b
update a set pid=@maxpid from a where id=@id insert into b select pid ,title,Num from a where id=@id fetch next from pid into @id end CLOSE pid DEALLOCATE pid GO
表1
ID PID 名称 编号,
1 a 00001
2 b 00002
3 c 00003
4 d 00004
5 e 00013
表2
PID 名称 编号,
1 a 00023
2 b 00024
3 c 00032
4 d 00033
5 e 00025要得到结果是
表1
ID PID 名称 编号,
1 6 a 00001
2 7 b 00002
3 8 c 00003
4 9 d 00004
5 10 e 00013
再把表1数据复制到表2中,表2是PID字段值是不允许重复(是唯一)
查询表2中pid最大值加1后,用该值修改表1中id最小并且pid为空的那行数据,然后把该数据复制到表2 ?
Max(表2pid)+1--->修改表1的pid(id最小并且pid为空那行)--->该后那行复制表1到表2--->重复操作???
CREATE proc bb
asdeclare @id int ,@maxpid int
set @id=0
set @maxpid=0declare pid cursor for
select id from a where pid is null order by id
for read only
open pid
fetch next from pid into @id
while @@fetch_status = 0
begin
select @maxpid=isnull(max(pid),0)+1 from b
update a set pid=@maxpid from a where id=@id insert into b select pid ,title,Num from a where id=@id fetch next from pid into @id
end
CLOSE pid
DEALLOCATE pid
GO