SQL强大的首要字Merge

介绍

Merge关键字是一个神奇之DML关键字。它当SQL Server
2008吃引入,它亦可将Insert,Update,Delete简单的连也同样句子。MSDN对于Merge的说明很的短小精悍:”根据与源表联接的结果,对目标表执行插入、更新或删除操作。例如,根据当任何一个表中找到的差异在一个表中插入、更新或删除行,可以本着个别个说明展开共同。”,通过这个描述,我们可见见Merge是关于对有数个说明内的数量进行操作的。可以想像发生,需要采用Merge的光景比如:

  • 数量并
  • 数量易
  • 基于源表对目标表开Insert,Update,Delete操作

语法

merge [into] [目标表]
using <源表>
on 条件
when matched 操作 
when not matched 操作;

举个例证

建立并个表t1,t2。

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    department VARCHAR(20),
    grade INT DEFAULT 0
);
CREATE TABLE t2 (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    department VARCHAR(20),
    grade INT DEFAULT 0
);

栽测试数据

INSERT  INTO dbo.t1
    ( id, name, department, grade )
    VALUES  ( 1, 'aaaa', 'd1', 60 ),
    ( 2, 'bbbb', 'd2', 70 ),
    ( 3, 'cccc', 'd2', 70 ),
    ( 4, 'dddd', 'd2', 70 ),
    ( 5, 'eeee', 'd2', 75 ),
    ( 6, 'ffff', 'd2', 70 ),
    ( 7, 'gggg', 'd2', 80 );
INSERT  INTO dbo.t2
    ( id, name, department, grade )
VALUES  ( 1, 'aaaa', 'd1', 60 ),
    ( 2, 'bbbb', 'd2', 70 ),
    ( 3, 'cccc', 'd3', 74 ),
    ( 4, 'dddd', 'd4', 77 ),
    ( 5, 'eeee', 'd5', 75 ),
    ( 6, 'ffff', 'd6', 70 ),
    ( 7, 'gggg', 'd7', 88 ),
    ( 8, 'hhhh', 'd8', 70 ),
    ( 9, 'iiii', 'd7', 70 ),
    ( 10, 'jjjj', 'd9', 70 ),
    ( 11, 'kkkk', 'd9', 70 );

始于数据

为此merge语句以t2表的多寡更新至t1表,如果t1表里有t2表有name字段相等的数(当然匹配原则可以生多独),就用t2表的department字段值更新t1阐明底department值,如果t2表的name字段值在t1表里不设有就是以t1表里安插入新的实践。

MERGE INTO dbo.t1
USING dbo.t2
ON dbo.t1.name = t2.name --匹配字段
WHEN MATCHED THEN UPDATE SET t1.department = t2.department --匹配有就更新
WHEN NOT MATCHED THEN INSERT VALUES(id,name,department,grade); --匹配没有就插入

履新后底t1表

当要看不发生还对t1说明开了怎么操作,还得借助OUTPUT关键字来查转,例如修改报告句如下

MERGE INTO dbo.t1
USING dbo.t2
ON dbo.t1.name = t2.name
WHEN MATCHED THEN UPDATE SET t1.department = t2.department
WHEN NOT MATCHED THEN INSERT VALUES(id,name,department,grade)
OUTPUT --显示操作变化
$action AS 操作类型,
Inserted.id AS 插入id,
Inserted.name AS 插入name,
Inserted.department AS 插入department,
Inserted.grade AS 插入grade;

实施结果如图

查操作变化

相关文章