SQL Server 2008 Merge语句实战：实现数据的增删改同步

-- 如果表存在则删除
if OBJECT_ID('Demo_AllProducts') is not null drop table Demo_AllProducts go 
-- 创建表结构
Create table Demo_AllProducts (
    PKID int not null identity(1,1) primary key ,
    DName Nvarchar(20) null,
    DCode NVarchar(30) null,
    DDate datetime null
)
go 
-- 插入初始数据 (SQL Server 2008支持多值INSERT)
Insert into Demo_AllProducts (DName,DCode,DDate) values 
('DemoA','AAA',GETDATE()), 
('DemoB','BBB',GETDATE()), 
('DemoC','CCC',GETDATE()), 
('DemoD','DDD',GETDATE()), 
('DemoE','EEE',GETDATE()); 

select * from Demo_AllProducts;

-- 创建分店表
if OBJECT_ID('Demo_Shop1_Product') is not null drop table Demo_Shop1_Product go 
Create table Demo_Shop1_Product (
    PKID int not null identity(1,1) primary key ,
    DName Nvarchar(20) null,
    DCode NVarchar(30) null,
    DDate datetime null
)
go 
-- 插入分店数据
Insert into Demo_Shop1_Product (DName,DCode,DDate) values 
('DemoA','AAA',GETDATE()), 
('DemoB','CCC',GETDATE()), -- 注意：这里代码写错了示例意图，通常应该是对应另一个编码，假设意在修改现有或新增。
                          -- 原文示例中DemoB对应CCC，而原总表BBB对应DemoB，这里假设分店有AAA(同名), CCC(原名DemoB), FFF(新)
                          -- 修正逻辑以符合下文解释：分店有 'DemoA/AAA', 'DemoB/CCC' (可能是笔误，按原文执行), 'DemoF/FFF'
                          -- 为严谨起见，以下代码严格按原文输入：
('DemoB','CCC',GETDATE()), 
('DemoF','FFF',GETDATE()); 

select * from Demo_Shop1_Product;

Merge Into Demo_AllProducts p -- 目标表别名p
using Demo_Shop1_Product s on p.DCode=s.DCode -- 关联条件
-- 当匹配时，如果名称不同则更新
When Matched and P.DName<>s.DName Then Update set P.DName=s.DName 
-- 当目标表中不存在（即分店有，总表没有）时插入
When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate) 
-- 当源表中不存在（即总表有，分店没有）时删除
When Not Matched By Source Then Delete;

Merge Into Demo_AllProducts p 
using Demo_Shop1_Product s on p.DCode=s.DCode 
When Matched and P.DName<>s.DName Then Update set P.DName=s.DName 
When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate);

-- 定义表变量存储输出日志
Declare @tableVarRecord Table (
    MPKID int not null identity(1,1) primary key ,
    PKID int null ,
    DName Nvarchar(20) null ,
    DCode NVarchar(30) null ,
    DDate datetime null )

Merge Into Demo_AllProducts p 
using Demo_Shop1_Product s on p.DCode=s.DCode 
When Matched and P.DName<>s.DName Then Update set P.DName=s.DName 
When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate) 
When Not Matched By Source Then Delete OUTPUT deleted.* INTO @tableVarRecord;

-- 查询影响总数
select @@ROWCOUNT as Count1, ROWCOUNT_BIG() as Count2 
-- 查看被删除的记录详情
select * from @tableVarRecord;