主健表A
ID name
1 a
2 b
3 c外健表 B
ID A_ID
1 1
2 1
3 1
要导入的表x
name
a
b
b
倒入后的外健表B
ID A_ID
1 1
2 1
3 1
4 1
5 2
6 2
ID name
1 a
2 b
3 c外健表 B
ID A_ID
1 1
2 1
3 1
要导入的表x
name
a
b
b
倒入后的外健表B
ID A_ID
1 1
2 1
3 1
4 1
5 2
6 2
drop table A
go
create table A(ID int,name varchar(10))
insert into A(ID,name) values(1,'a')
insert into A(ID,name) values(2,'b')
insert into A(ID,name) values(3,'c')
goif object_id('pubs..B') is not null
drop table B
go
create table B(ID int,A_ID varchar(10))
insert into B(ID,A_ID) values(1,1)
insert into B(ID,A_ID) values(2,1)
insert into B(ID,A_ID) values(3,1)
goif object_id('pubs..X') is not null
drop table X
go
create table X(name varchar(10))
insert into X(name) values('a')
insert into X(name) values('b')
insert into X(name) values('b')
godeclare @id as int
declare @i as int
declare @cnt as int
set @i = 1
select px = identity(int,1,1) , A.id into test from A,X where A.name = X.name
select @cnt = count(*) from testwhile @i <= @cnt
begin
select @id = max(b.id) + 1 from b
insert into B(id , A_ID) select @id , id from test where px = @i
set @i = @i + 1
end select * from bdrop table A,B,X,test/*
ID A_ID
----------- ----------
1 1
2 1
3 1
4 1
5 2
6 2(所影响的行数为 6 行)
*/
drop table A
go
create table A(ID int,name varchar(10))
insert into A(ID,name) values(1,'a')
insert into A(ID,name) values(2,'b')
insert into A(ID,name) values(3,'c')
goif object_id('pubs..B') is not null
drop table B
go
create table B(ID int,A_ID varchar(10))
insert into B(ID,A_ID) values(1,1)
insert into B(ID,A_ID) values(2,1)
insert into B(ID,A_ID) values(3,1)
goif object_id('pubs..X') is not null
drop table X
go
create table X(name varchar(10))
insert into X(name) values('a')
insert into X(name) values('b')
insert into X(name) values('b')
godeclare @id as int
declare @i as int
declare @cnt as int
set @i = 1
select px = identity(int,1,1) , A.id into test from A,X where A.name = X.name
select @cnt = count(*) from testwhile @i <= @cnt
begin
select @id = max(b.id) + 1 from b
if @id is null set @id = 1
insert into B(id , A_ID) select @id , id from test where px = @i
set @i = @i + 1
end select * from bdrop table A,B,X,test/*
ID A_ID
----------- ----------
1 1
2 1
3 1
4 1
5 2
6 2(所影响的行数为 6 行)
*/
ID name
1 a
2 b
3 c外健表 B
ID A_ID
1 1
2 1
3 1
要导入的表x
name
a
b
f
倒入后的外健表B
ID A_ID
1 1
2 1
3 1
4 1
5 2
6 4
要导入的表x ,改为f,也不能插入主表
name
a
b
f