我的要求可以用一个例子来说明一下
*******************************
主表 tbl_F有一条记录
F_ID F_1 F_1 ... F_n
1 "a" "b" ... "n" 子表 tbl_S相对应有二条记录
S_ID S_FID S_1 S_2
1 1 "xx" "zz"
2 1 "xx" "zz"
*******************************
其中,主表F_ID 和从表S_FID 字段关联备份表主从结构字段相同
*******************************
备份主表 bck_F 结构字
F_ID F_1 F_1 ... F_n 备份子表 bck_S 结构字
S_ID S_FID S_1 S_2
*******************************
请问该存储过程如何做?
*******************************
主表 tbl_F有一条记录
F_ID F_1 F_1 ... F_n
1 "a" "b" ... "n" 子表 tbl_S相对应有二条记录
S_ID S_FID S_1 S_2
1 1 "xx" "zz"
2 1 "xx" "zz"
*******************************
其中,主表F_ID 和从表S_FID 字段关联备份表主从结构字段相同
*******************************
备份主表 bck_F 结构字
F_ID F_1 F_1 ... F_n 备份子表 bck_S 结构字
S_ID S_FID S_1 S_2
*******************************
请问该存储过程如何做?
insert into bck_F(f_id,f_1,f_2)
select f_id,f_1,f_2 from tbl_f where 条件
2.按备份主表的f_id来插入备份子表
insert into bck_s(s_id,s_fid,s_1,s_2)
select s_id,s_fid,s_1,s_2 from tbl_s where s_fid in (select f_id from bck_f)
create table tbl_f(f_id int,f_1 char(1),f_2 char(1))
create table bck_F(f_id int,f_1 char(1),f_2 char(1))
insert into tbl_f(f_id,f_1,f_2)
select 1,'a','b'
create table tbl_s(s_id int,s_fid int,s_1 char(2),s_2 char(2))
create table bck_s(s_id int,s_fid int,s_1 char(2),s_2 char(2))
insert into tbl_s(s_id,s_fid,s_1,s_2)
select 1,1,'xx','zz'
union all select 2,1,'xx','zz'--建立复制存储过程
create proc usp_copydata
as
begin
insert into bck_F(f_id,f_1,f_2)
select f_id,f_1,f_2 from tbl_finsert into bck_s(s_id,s_fid,s_1,s_2)
select s_id,s_fid,s_1,s_2 from tbl_s where s_fid in (select f_id from bck_f)
end--执行
exec usp_copydata--显示执行后结果
select * from bck_f
select * from bck_s
Insert bck_F(F_1, F_2,... F_n) Select F_1, F_2,... F_n From tbl_F
Insert bck_S( S_FID, S_1, S_2 ) Select S_FID, S_1,S_2 From tbl_S
SET IDENTITY_Insert bck_F ON
Insert bck_F(F_ID,F_1, F_2,... F_n) Select F_ID,F_1, F_2,... F_n From tbl_F
SET IDENTITY_Insert bck_F OFF
SET IDENTITY_Insert bck_S ON
Insert bck_S(S_ID,S_FID, S_1, S_2 ) Select S_ID,S_FID, S_1,S_2 From tbl_S
SET IDENTITY_Insert bck_S OFF
注意列名需要顯示的列出來,不能直接用*.