SQL之MergeInto用法--可替代updateinsert(文章都是以SqlServ。。。_百 ...

2023年8月3日发(作者：)

SQL之MergeInto⽤法--可替代updateinsert（⽂章都是以SqlServ。。。 ⼤数据的SQL执⾏优化时，对于update和insert语句可以尝试使⽤merge into语句提⾼执⾏效率。 简单来说，对于已存在（符合on中的条件）的数据执⾏更新操作，不存在的数据执⾏插⼊操作。 基本语法：merge into ⽬标表 ausing 源表 bon(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) alias2ON (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]GOCREATE 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]GOSET ANSI_PADDING OFFGOSET 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] OFFSET 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] OFFSourceTable数据如下：

TargetTable的数据如下

merge into语句如下：merge into TargetTable target

using (select Id,[Desc] from SourceTable) sourceon( = )

when matched then

update

set target.[Desc] = source.[Desc]when not matched theninsert([Desc]) values (source.[Desc]);此时TargetTable的数据如下：

实际应⽤如下：修改或插⼊⼀条数据时CREATE PROC MergeIntoTargetTable1 @Id int, @Desc varchar(20)ASBEGIN merge into TargetTable t

using(select @Id as id,@Desc as [Desc]) s

on ( = )

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 readonlyASBEGIN merge into TargetTable t

using @dt s

on = when matched then

update set t.[desc] = s.[Desc] when not matched then

insert values(s.[Desc]);ENDmerge into的其他⽤法：Merge语句还有⼀个强⼤的功能是通过OUTPUT⼦句，可以将刚刚做过变动的数据进⾏输出merge into TargetTable tusing (select Id,[Desc] from SourceTable) son( = )

when matched then

update

set t.[Desc] = s.[Desc]when not matched theninsert values (s.[Desc])when not matched by source

then delete

output $ACTION as [Action], as 插⼊的id,Inserted.[Desc] as 插⼊的DESC;

2023年8月3日发(作者：)

SQL之MergeInto⽤法--可替代updateinsert（⽂章都是以SqlServ。。。 ⼤数据的SQL执⾏优化时，对于update和insert语句可以尝试使⽤merge into语句提⾼执⾏效率。 简单来说，对于已存在（符合on中的条件）的数据执⾏更新操作，不存在的数据执⾏插⼊操作。 基本语法：merge into ⽬标表 ausing 源表 bon(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) alias2ON (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]GOCREATE 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]GOSET ANSI_PADDING OFFGOSET 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] OFFSET 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] OFFSourceTable数据如下：

TargetTable的数据如下

merge into语句如下：merge into TargetTable target

using (select Id,[Desc] from SourceTable) sourceon( = )

when matched then

update

set target.[Desc] = source.[Desc]when not matched theninsert([Desc]) values (source.[Desc]);此时TargetTable的数据如下：

实际应⽤如下：修改或插⼊⼀条数据时CREATE PROC MergeIntoTargetTable1 @Id int, @Desc varchar(20)ASBEGIN merge into TargetTable t

using(select @Id as id,@Desc as [Desc]) s

on ( = )

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 readonlyASBEGIN merge into TargetTable t

using @dt s

on = when matched then

update set t.[desc] = s.[Desc] when not matched then

insert values(s.[Desc]);ENDmerge into的其他⽤法：Merge语句还有⼀个强⼤的功能是通过OUTPUT⼦句，可以将刚刚做过变动的数据进⾏输出merge into TargetTable tusing (select Id,[Desc] from SourceTable) son( = )

when matched then

update

set t.[Desc] = s.[Desc]when not matched theninsert values (s.[Desc])when not matched by source

then delete

output $ACTION as [Action], as 插⼊的id,Inserted.[Desc] as 插⼊的DESC;