SQL之Merge Into用法--可替代update insert（文章都是以SqlServer为例）

     大数据的SQL执行优化时，对于update和insert语句可以尝试使用merge into语句提高执行效率。

     简单来说，对于已存在（符合on中的条件）的数据执行更新操作，不存在的数据执行插入操作。

     基本语法：

merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
when matched then update set a.更新字段=b.字段
when  not matched then insert into a(字段1,字段2……)values(值1,值2……)；

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
UPDATE 
SET col1 = col1_val1, 
col2 = col2_val2 
WHEN NOT MATCHED THEN 
INSERT (column_list) VALUES (column_values); 

其中，table_name 指的是更新的表，using()里边的指的是数据来源表/视图/子查询结果集，condition指的是连接条件，如果满足连接条件，set 字段1=值1，字段2=值2...

如果条件不满足停止更新进行插入操作。语句必须以分号结尾。

举例 

现有两个表SourceTable和TargetTabel,具体建表语句及插入数据语句如下：

CREATE TABLE [dbo].[SourceTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Desc] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TargetTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Desc] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[SourceTable] ON 

INSERT [dbo].[SourceTable] ([Id], [Desc]) VALUES (1, N'test1')
INSERT [dbo].[SourceTable] ([Id], [Desc]) VALUES (2, N'test2')
INSERT [dbo].[SourceTable] ([Id], [Desc]) VALUES (3, N'test3')
SET IDENTITY_INSERT [dbo].[SourceTable] OFF
SET IDENTITY_INSERT [dbo].[TargetTable] ON 

INSERT [dbo].[TargetTable] ([Id], [Desc]) VALUES (1, N'测试1')
INSERT [dbo].[TargetTable] ([Id], [Desc]) VALUES (2, N'测试2')
SET IDENTITY_INSERT [dbo].[TargetTable] OFF

SourceTable数据如下：

 

 

 

 

 TargetTable的数据如下

 

 

 

 merge into语句如下：

merge into TargetTable target 
using (select Id,[Desc] from SourceTable) source
on(target.Id = Source.Id) 
when matched then 
update 
set target.[Desc] = source.[Desc]
when not matched then
insert([Desc]) values (source.[Desc]);

此时TargetTable的数据如下：

 

 实际应用如下：

修改或插入一条数据时

CREATE PROC MergeIntoTargetTable1
   @Id int,
   @Desc varchar(20)
AS
BEGIN
  merge into TargetTable t 
  using(select @Id as id,@Desc as [Desc]) s 
  on (t.id = s.id) 
  when matched then 
  update set [desc] = s.[Desc]
  when not matched then 
  insert ([Desc]) values(s.[Desc]);
END

当修改多条数据时，使用自定义表类型加merge into语句，具体代码如下：

CREATE TYPE SourceTableType AS TABLE 
(
    Id int,
    [Desc] varchar(20)
)

CREATE PROC MergeIntoTargetTable2
   @dt SourceTableType readonly
AS
BEGIN
  merge into TargetTable t 
  using @dt s 
  on t.id = s.id
  when matched then 
  update set t.[desc] = s.[Desc]
  when not matched then 
  insert values(s.[Desc]);
END

merge into的其他用法：

Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出

merge into TargetTable t
using (select Id,[Desc] from SourceTable) s
on(t.Id = s.Id) 
when matched then 
update 
set t.[Desc] = s.[Desc]
when not matched then
insert values (s.[Desc])
when not matched by source 
then delete 
output $ACTION as [Action],Inserted.id as 插入的id,
Inserted.[Desc] as 插入的DESC;

 文章部分摘自https://blog.csdn.net/spw55381155/article/details/79891305；