34,588
社区成员
发帖
与我相关
我的任务
分享
go
create table t1(
C1 INT,
C2 INT,
C3 INT
)
go
create table t2(
C1 INT,
C2 INT,
C3 INT,
D1 INT
)
declare @str varchar(2000)
set @str=''
select @str=@str+','+name from syscolumns where id=object_id('t1')
and exists(select 1 from(select name
from syscolumns where id=object_id('t2'))a where a.name=name)
set @str='insert t2 select '+right(@str,len(@str)-1)+' from t1'
print @str
--insert t2 select C1,C2,C3 from t1
--你只需动态执行
exec(@str)
insert T2
select C1,C2,C3 from T1
--查询T1中存在的T2的记录
insert T2
select * from T1 a
where exists(select 1 from T2 b where a.C1=b.c1 and a.C2=b.c2 and a.C3=b.c3)
--查询T1中不存在的T2的记录
insert T2
select * from T1 a
where not exists(select 1 from T2 b where a.C1=b.c1 and a.C2=b.c2 and a.C3=b.c3)