日期:2014-05-16 浏览次数:20789 次
--这玩意不经常练习还真容易忘,哎
create table A
(
id int,
Name nvarchar(20),
Remark nvarchar(50)
)
create table B
(
id int,
Name nvarchar(20),
Address nvarchar(50),
Remark nvarchar(50)
)
insert into A values(1,'张三','测试张三');
insert into A values(2,'李四','测试李四');
insert into A values(3,'王五','测试王五');
insert into B values(1,'张三','测试张三','');
insert into B values(2,'赵六','测试赵六','');
insert into B values(3,'孙七','测试孙七','');
--无需两个表结果完全一致
insert into B (ID,Name)
select id,Name from
(
select id,Name from A
except
select id,Name from B
)t
MERGE B --要处理的表
USING A --参照的表
ON A.ID=B.ID and A.Name=B.Name --关联条件
WHEN NOT MATCHED THEN INSERT(id,Name) VALUES(A.ID,A.Name); --如果要处理表没有参照表上的记录,则插入
--WHEN MATCHED THEN UPDATE SET A.ACOL=B.BCOL --如果记录匹配,就更新目标表的匹配行
--WHEN NOT MATCHED BY SOURCE THEN DELETE --如果要处理表的记录在参照表上不存在,则删除
insert into b
select *
from a
left join b
on a.[a] = b.[a] and a.[b] = b.[b]
where a is null and b is null