34,588
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#1') is not null
drop table #1;
go
create table #1 (id int, c char(1));
go
insert into #1 values(1,'a');
insert into #1 values(2,'a');
insert into #1 values(3,'a');
go
if OBJECT_ID('tempdb..#2') is not null
drop table #2;
go
create table #2 (id int, c char(1));
go
insert into #2 values(1,'a');
insert into #2 values(2,'a');
insert into #2 values(4,'a');
go
-- 方法 1
-- 查询 #1 和 #2 中重复的
select id,c from #1 where id in (select id from #2);
-- 插入不重复的
insert into #2 select id,c from #1 where id not in (select id from #2);
-- 方法 2(SQL Server 2008 适用)
-- 用 output 子句输出重复的($action=update)
merge into #2 t using #1 s
on t.id=s.id
when matched then
update set t.c=t.c
when not matched then
insert values(s.id,s.c)
output $action,deleted.*;
select * from #1;
select * from #2;
insert into @tb select *from @ta where FNAME not in(select FNAME from @tb)