MERGE关键字可以用来同步两张表的数据
可以一次完成insert，update，delete的功能
创建测试表
CREATE TABLE [dbo].[MERGE1](
[id] [int] NOT NULL,
[name] nvarchar NULL,
[age] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MERGE2](
[id] [int] NOT NULL,
[name] nvarchar NULL
) ON [PRIMARY]
插入测试数据
GO
INSERT [dbo].[MERGE1] ([id], [name], [age]) VALUES (1, N'A1', 20)
GO
INSERT [dbo].[MERGE1] ([id], [name], [age]) VALUES (2, N'B1', 33)
GO
INSERT [dbo].[MERGE1] ([id], [name], [age]) VALUES (3, N'C1', 79)
GO
INSERT [dbo].[MERGE1] ([id], [name], [age]) VALUES (6, N'Z1', 6)
GO
INSERT [dbo].[MERGE2] ([id], [name]) VALUES (1, N'A2')
GO
INSERT [dbo].[MERGE2] ([id], [name]) VALUES (2, N'B2')
GO
INSERT [dbo].[MERGE2] ([id], [name]) VALUES (3, N'C2')
GO
INSERT [dbo].[MERGE2] ([id], [name]) VALUES (4, N'D2')
GO
执行
MERGE INTO MERGE1 m1 目标表
USING MERGE2 m2 源表
ON (m1.id=m2.id) 条件判断
如果存在则更新某个字段
WHEN MATCHED THEN UPDATE SET m1.name=m2.name
如果不存在则把原表数据插入目标表
WHEN NOT MATCHED THEN INSERT VALUES (m2.ID, m2.name,0)
如果目标表存在但是原表不存在则删除目标表数据
WHEN NOT MATCHED BY SOURCE THEN DELETE;
