34,587
社区成员
发帖
与我相关
我的任务
分享
DECLARE @new_a_id int
declare @a table(a_id int IDENTITY(1,1),a_data int)
insert into @a select 1000
insert into @a select 2000
declare @b table(b_id int IDENTITY(1,1),b_a_id int,b_data int )
insert into @b select 1,1111
insert into @b select 1,2222
insert into @b select 1,3333
insert into @b select 2,4444
insert into @b select 2,5555
insert into @b select 2,6666
declare @c table(c_id int IDENTITY(1,1),c_a_id int,c_b_id_1 int,c_b_id_2 int)
insert into @c select 1,1,2
insert into @c select 1,1,3
insert into @c select 2,4,5
insert into @c select 2,5,6
insert into @a(a_data) select a_data from @a where a_id=1
SET @new_a_id = @@IDENTITY;
insert into @b(b_a_id,b_data) select @new_a_id,b_data from @b where b_a_id=1
select * from @a
select * from @b
select * from @c
结果:
a_id a_data
----------- -----------
1 1000
2 2000
3 1000
b_id b_a_id b_data
----------- ----------- -----------
1 1 1111
2 1 2222
3 1 3333
4 2 4444
5 2 5555
6 2 6666
7 3 1111
8 3 2222
9 3 3333
c_id c_a_id c_b_id_1 c_b_id_2
----------- ----------- ----------- -----------
1 1 1 2
2 1 1 3
3 2 4 5
4 2 5 6
这时候c表还没有做批量复制,我希望得到如下的c表:
c_id c_a_id c_b_id_1 c_b_id_2
----------- ----------- ----------- -----------
1 1 1 2
2 1 1 3
3 2 4 5
4 2 5 6
5 3 7 8
6 3 7 9
insert into @c(c_a_id,c_b_id_1,c_b_id_2)
select
@new_a_id,
(select b_id from @b where b_data=(select b_data from @b where b_id=c_b_id_1) and b_a_id=@new_a_id) as c_b_id_1,
(select b_id from @b where b_data=(select b_data from @b where b_id=c_b_id_2) and b_a_id=@new_a_id) as c_b_id_2
from @c where c_a_id=1