我有一个t1、t2的主从表:
t1主表
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4
t2从表(id为主键,id1与t1.id关联):
id id1 quantity
1 1 20
2 1 30
3 1 40
4 2 100
5 2 200
6 3 900有一个主从表t3和t4:
t3主表(id int IDENTITY(1,1) NOT NULL ,code varchar(15),listdate datetime,sign int)t4从表(id int IDENTITY(1,1) NOT NULL ,id1 int ,quantity int)注:t4.id1与t3.id是关联的。我想把t1表insert into到t3表里:
insert into t3(code,listdate,sign) select code,listdate,sign from t1set @newid = SCOPE_IDENTITY()同时把t2表insert into到t4表里:
insert into t4(id1,quantity) select @newid,quantity from t2 where id1=@oldid因为这个涉及到循环的问题,我的想法是把t1表从第一条开始循环,取出t1.id,
先把t1表插入t3表;再把t2表插入t4表,t2表插入t4表时,where的条件是t2.id1=t1.id,这整个循环怎么写?最好不要用到游标来写。
t1主表
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4
t2从表(id为主键,id1与t1.id关联):
id id1 quantity
1 1 20
2 1 30
3 1 40
4 2 100
5 2 200
6 3 900有一个主从表t3和t4:
t3主表(id int IDENTITY(1,1) NOT NULL ,code varchar(15),listdate datetime,sign int)t4从表(id int IDENTITY(1,1) NOT NULL ,id1 int ,quantity int)注:t4.id1与t3.id是关联的。我想把t1表insert into到t3表里:
insert into t3(code,listdate,sign) select code,listdate,sign from t1set @newid = SCOPE_IDENTITY()同时把t2表insert into到t4表里:
insert into t4(id1,quantity) select @newid,quantity from t2 where id1=@oldid因为这个涉及到循环的问题,我的想法是把t1表从第一条开始循环,取出t1.id,
先把t1表插入t3表;再把t2表插入t4表,t2表插入t4表时,where的条件是t2.id1=t1.id,这整个循环怎么写?最好不要用到游标来写。
select t3.id,quantity from t1,t2,t3
where t1.id=t2.id1 and t1.code=t3.code
declare @t1 table (id int,code varchar(15),listdate datetime,sign int)
insert into @t1
select 1,'A00201101020001','2011-01-02',1 union all
select 2,'A00201101030002','2011-01-03',1 union all
select 3,'B00201101010001','2011-01-01',2 union all
select 4,'C00201102050003','2011-02-05',3 union all
select 5,'C00201101300004','2011-01-30',3 union all
select 6,'D00201101180003','2011-01-18',4 union all
select 7,'D00201101180004','2011-01-18',4declare @t2 table (id int,id1 int ,quantity int)
insert into @t2
select 1,1,20 union all
select 2,1,30 union all
select 3,1,40 union all
select 4,2,100 union all
select 5,2,200 union all
select 6,3,900declare @t3 table (id int IDENTITY(1,1) NOT NULL ,code varchar(15),listdate datetime,sign int)
declare @t4 table (id int IDENTITY(1,1) NOT NULL ,id1 int ,quantity int)select * from @t1
select * from @t2declare @id int,@oldid int,@newid int
while exists(select * from @t1 )
begin
select @oldid=min(id) from @t1
select @id=@oldid
insert into @t3(code,listdate,sign)
select code,listdate,sign from @t1 set @newid = SCOPE_IDENTITY()
insert into @t4(id1,quantity)
select @newid,quantity from @t2 where id1=@id
--exec p_SL_CounterVendition_update_loop @id output,@msg output,'''+@strDBcode+'''
delete @t1 where id=@oldid
delete @t2 where id1=@oldid
end
select * from @t3
select * from @t4但是问题有两个问题,@t3多出好多重复数据出来,@t4的id1的数据对不上@t3
@t3主表
id code listdate sign
6 A00201101020001 2011-01-02 1
7 A00201101030002 2011-01-03 1
8 B00201101010001 2011-01-01 2
9 C00201102050003 2011-02-05 3
10 C00201101300004 2011-01-30 3
11 D00201101180003 2011-01-18 4
12 D00201101180004 2011-01-18 4@t4从表:
id id1 quantity
6 6 20
7 6 30
8 6 40
9 7 100
10 7 200
11 8 900这样@t3、@t4与@t1、@t2数据一致。
insert into #3(id, code,listdate,sign) select id, code,listdate,sign from #1
set identity_insert #3 offset identity_insert #4 on
insert into #4(id, id1,quantity) select * from #2
set identity_insert #4 off
insert into t1 select 1,'A00201101020001','2011-01-02',1
insert into t1 select 2,'A00201101030002','2011-01-03',1
insert into t1 select 3,'B00201101010001','2011-01-01',2
insert into t1 select 4,'C00201102050003','2011-02-05',3
insert into t1 select 5,'C00201101300004','2011-01-30',3
insert into t1 select 6,'D00201101180003','2011-01-18',4
insert into t1 select 7,'D00201101180004','2011-01-18',4
create table t2(id int,id1 int,quantity int)
insert into t2 select 1,1,20
insert into t2 select 2,1,30
insert into t2 select 3,1,40
insert into t2 select 4,2,100
insert into t2 select 5,2,200
insert into t2 select 6,3,900
go
ALTER TABLE dbo.t2 ADD CONSTRAINT FK_t2_t1 FOREIGN KEY(id1) REFERENCES dbo.t1(id) ON UPDATE NO ACTION ON DELETE NO ACTION
go
create table t3(id int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,code varchar(15),listdate datetime,[sign] int)
create table t4(id int IDENTITY(1,1) NOT NULL ,id1 int ,quantity int)
ALTER TABLE dbo.t4 ADD CONSTRAINT FK_t4_t3 FOREIGN KEY(id1) REFERENCES dbo.t3(id) ON UPDATE NO ACTION ON DELETE NO ACTION
go
SET IDENTITY_INSERT dbo.t3 ON
insert into t3(id,code,listdate,[sign]) select * from t1
SET IDENTITY_INSERT dbo.t3 OFF
SET IDENTITY_INSERT dbo.t4 ON
insert into t4(id,id1,quantity) select * from t2
SET IDENTITY_INSERT dbo.t4 OFF
select * from t3
select * from t4
go
drop table t2,t1,t4,t3
/*
id code listdate sign
----------- --------------- ----------------------- -----------
1 A00201101020001 2011-01-02 00:00:00.000 1
2 A00201101030002 2011-01-03 00:00:00.000 1
3 B00201101010001 2011-01-01 00:00:00.000 2
4 C00201102050003 2011-02-05 00:00:00.000 3
5 C00201101300004 2011-01-30 00:00:00.000 3
6 D00201101180003 2011-01-18 00:00:00.000 4
7 D00201101180004 2011-01-18 00:00:00.000 4(7 行受影响)id id1 quantity
----------- ----------- -----------
1 1 20
2 1 30
3 1 40
4 2 100
5 2 200
6 3 900(6 行受影响)
*/
insert into @t1
select 1,'A00201101020001','2011-01-02',1 union all
select 2,'A00201101030002','2011-01-03',1 union all
select 3,'B00201101010001','2011-01-01',2 union all
select 4,'C00201102050003','2011-02-05',3 union all
select 5,'C00201101300004','2011-01-30',3 union all
select 6,'D00201101180003','2011-01-18',4 union all
select 7,'D00201101180004','2011-01-18',4declare @t2 table (id int,id1 int ,quantity int)
insert into @t2
select 1,1,20 union all
select 2,1,30 union all
select 3,1,40 union all
select 4,2,100 union all
select 5,2,200 union all
select 6,3,900declare @t3 table (id int IDENTITY(1,1) NOT NULL ,code varchar(15),listdate datetime,sign int)
declare @t4 table (id int IDENTITY(1,1) NOT NULL ,id1 int ,quantity int)select * from @t1
select * from @t2declare @id int,@oldid int,@newid int
while exists(select * from @t1 )
begin
select @oldid=min(id) from @t1
select @id=@oldid
insert into @t3(code,listdate,sign)
select code,listdate,sign from @t1 where id = @id
set @newid = SCOPE_IDENTITY()
insert into @t4(id1,quantity)
select @newid,quantity from @t2 where id1=@id
--exec p_SL_CounterVendition_update_loop @id output,@msg output,'''+@strDBcode+'''
delete @t1 where id=@oldid
delete @t2 where id1=@oldid
end
select * from @t3
select * from @t4
不能用下列的语句:
SET IDENTITY_INSERT dbo.t3 ON
SET IDENTITY_INSERT dbo.t3 OFF如果是这样,我干脆在t3、t4建立id时,就不要用到:id int IDENTITY(1,1) NOT NULL ,我就不用费那么大的神来实现我想要的结果了。