sql server 2008语言基础: 再回首之Sql 2008的merge关键字

数据脚本为

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
  (1, 'cust 1', '(111) 111-1111', 'address 1'),
  (2, 'cust 2', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (4, 'cust 4', '(444) 444-4444', 'address 4'),
  (5, 'cust 5', '(555) 555-5555', 'address 5');

IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;
GO

CREATE TABLE dbo.CustomersStage
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);

INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
VALUES
  (2, 'AAAAA', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (5, 'BBBBB', 'CCCCC', 'DDDDD'),
  (6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
  (7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

 

上面的是目标表T， 下面的是源表S。 仔细观察， 发现t里面没有custid为6,7的行， 而且2,5行不一样。

那么， 用merge关键字的操作。 将会在T表中插入S表的custid为6,7两行， 并且修改2,5两行。

--1. 把customersStage表(来源表)的内容的合并到Customers表(目标表)中. 更具体的说,
--假设现在还想增加一些还不存在的客户, 和更新已经存在的客户的属性.
--仔细观看已有的两张表。 发现目标表里面没有6,7. 并且2,5的属性有改动。
merge into customers as t
using customersStage as s
on t.custid=s.custid
when matched then update set t.companyname=s.companyname, t.phone=s.phone, t.[address]=s.[address]
when not matched then insert (custid, companyname, phone, [address]) 
                        values( s.custid,s.companyname, s.phone, s.[address])
;

得到结果

多了两行记录并且修改了两行记录。

 

--上面介绍了merge子句的两种匹配方式。
-- when matched 匹配时。 when not matched不匹配时。
-- 第三种子句。 when not mateched by source. 用于定义对于目标表的一个行， 在来源表中没有
-- 与之匹配的行时， 应该采取的操作。 一般去情况下是删除。
-- --如果target里面有, 但是源表没有的就删除. 有点类似于inner join的笛卡尔乘积,
-- 左边表里面存着记录, 但是右边表没有. 还是删除掉

现在我们重新新建这两张表。

发现目标表中有， 但是源表中没有的数据， 那么就删除掉。 发现T表中的1和4 要中枪了。

merge into customers as t
using customersStage as s
on t.custid=s.custid
when matched then update set t.companyname=s.companyname, t.phone=s.phone, t.[address]=s.[address]
when not matched then insert (custid, companyname, phone, [address])
                        values( s.custid,s.companyname, s.phone, s.[address])
when not matched by source then delete
;

得到和相比较。 发现1和4没了。

 

好了。 现在回到第一个例子。 这个例子中， 需要更新已经存在的客户和增加不存在的客户。

可是， 在重写现有客户的属性之前， 语句没有检查列值是否真的发生过变化。 也就是说， 即使来源表和目标表完全相同， 仍然要修改客户行。 用and选项能够为不同的操作子句增加谓词条件， 以避免不必要的系统开销。

merge into customers as t
using customersStage as s
on t.custid=s.custid
when matched and (
    t.companyname<>s.companyname
    or t.phone<>s.phone
    or t.[address]<>s.[address]
)
then update set t.companyname=s.companyname, t.phone=s.phone, t.[address]=s.[address]
when not matched then insert (custid, companyname, phone, [address])
                        values( s.custid,s.companyname, s.phone, s.[address])
when not matched by source then delete
;