merge语句_SQL Server MERGE语句概述和示例

最新推荐文章于 2024-08-01 15:05:06 发布

culuo4781

最新推荐文章于 2024-08-01 15:05:06 发布

阅读量2k

点赞数

文章标签：触发器 java mysql python 数据库

原文链接：https://www.sqlshack.com/sql-server-merge-statement-overview-and-examples/

版权

merge语句

In this article, we will review SQL Server MERGE statement, alternatives to MERGE statement, different clauses and examples of MERGE statement in SQL Server.

在本文中，我们将介绍SQL Server MERGE语句，MERGE语句的替代方法，SQL Server中的MERGE语句的不同子句和示例。

We will cover the following topics about SQL Server Merge Statement with some interesting examples in this post:

在本文中，我们将通过一些有趣的示例介绍有关SQL Server Merge Statement的以下主题：

Introduction and Syntax of Merge Statement in SQL Server
SQL Server中的合并语句的简介和语法
Using Merge Statement to update, insert and delete rows in SQL Server tables
使用合并语句更新，插入和删除SQL Server表中的行
Working with TOP clause in Merge Statement
在合并语句中使用TOP子句
OUTPUT clause in Merge Statement
合并语句中的OUTPUT子句
Merge with triggers in SQL Server
与SQL Server中的触发器合并

合并声明简介 (Merge Statement Introduction)

MERGE statement is used to synchronize two tables by inserting, deleting, and updating the target table rows based on the join condition with the source table. Let us discuss a few examples on the MERGE statement using demo tables.

MERGE语句用于通过基于连接条件和源表的插入，删除和更新目标表行来同步两个表。让我们使用演示表讨论有关MERGE语句的一些示例。

MERGE的语法 (Syntax of MERGE)

Below is the syntax of the MERGE statement in SQL Server.

下面是SQL Server中MERGE语句的语法。

MERGE TOP (value) <target_table> 
USING <table_source>   
ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ <output_clause> ]  
    [ OPTION ( <query_hint> ) ]      
;

Here I am creating sample tables to show different examples of the MERGE statement.

在这里，我正在创建示例表以显示MERGE语句的不同示例。

Use below T-SQL script to create tables and insert sample data into tables.

使用下面的T-SQL脚本创建表并将示例数据插入表中。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')

使用MERGE更新匹配的行 (Using MERGE to update matched rows)

WHEN MATCHED clause in SQL Server MERGE statement is used to update, delete the rows in the target table when the rows are matched with the source table based on the join condition. In this case, Locations is the target table, Locations_stage is the source table and the column LocationID is used in the join condition. Please refer to the below T-SQL script for updating matched rows using WHEN MATCHED clause.

SQL Server MERGE语句中的WHEN MATCHED子句用于更新时，根据联接条件将行与源表匹配时，删除目标表中的行。在这种情况下，Locations是目标表，Locations_stage是源表，并且在连接条件中使用了LocationID列。请参考下面的T-SQL脚本，以使用WHEN MATCHED子句更新匹配的行。

MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName;

Rows with LocationID 1 and 3 are matched in the target and source table as per the join condition and the value of LocationName in the target was updated with the value of LocationName in the source table for both rows.

根据连接条件，目标表和源表中具有LocationID 1和3的行匹配，并且两行的目标表中LocationName的值都用源表中的LocationName值进行了更新。

We can also use additional search condition along with “WHEN MATCHED” clause in SQL Server MERGE statement to update only rows that match the additional search condition.

我们还可以将附加搜索条件与SQL Server MERGE语句中的“ WHEN MATCHED”子句一起使用，以仅更新与附加搜索条件匹配的行。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED AND T.LocationID =3 THEN
UPDATE SET LocationName=S.LocationName;
 
 
select * from Locations

SQL Server merge example search condition

We can see that the merge statement did not update the row with LocationID 1 as it did not satisfy the additional search condition specified along with the WHEN MATCHED clause.

我们可以看到merge语句不满足LocationID为1的行的更新，因为它不满足WHEN MATCHED子句指定的附加搜索条件。

At most, we can specify only two WHEN MATCHED clauses in the MERGE statement. If two WHEN MATCHED clauses are specified, one clause must have an update operation and the other one must use delete operation. Please refer to below T-SQL script for the example for the MERGE statement with two WHEN MATCHED clauses.

最多只能在MERGE语句中指定两个WHEN MATCHED子句。如果指定了两个WHEN MATCHED子句，则一个子句必须具有更新操作，而另一个子句必须使用delete操作。请参考下面的T-SQL脚本以获取带有两个WHEN MATCHED子句的MERGE语句的示例。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED AND T.LocationID =3 THEN
DELETE 
WHEN MATCHED AND T.LocationID =1 THEN
UPDATE SET LocationName=S.LocationName;

SQL Server merge example with two WHEN MATCHED clauses

Note: We cannot use the same DML operation in both WHEN MATCHED clauses.注意：我们不能在两个WHEN MATCHED子句中使用相同的DML操作。

When there is more than one row in the source table that matches the join condition, the update in SQL Server MERGE statement fails and returns error “The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

当源表中有多行符合联接条件时，SQL Server MERGE语句中的更新将失败，并返回错误消息“ MERGE语句尝试多次更新或删除同一行。当目标行与多个源行匹配时，就会发生这种情况。 MERGE语句不能多次更新/删除目标表的同一行。优化ON子句以确保目标行最多匹配一个源行，或使用GROUP BY子句对源行进行分组。”

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street'),(1,'James Street')
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName;

SQL Server merge example for multiple rows in source table

使用MERGE在目标表中插入行 (Using MERGE to insert rows in Target table)

WHEN NOT MATCHED BY TARGET clause is used to insert rows into target table that does not match join condition with a source table. WHEN NOT MATCHED BY TARGET clause can be specified only once in the SQL Server MERGE statement.

WHEN NOT MATCHED BY TARGET子句用于将不符合联接条件的行插入源表。在SQL Server MERGE语句中只能将WHEN NOT MATCHED BY TARGET子句指定一次。

For example, the row with LocationID = 4 in the table Locations_stage does not match join condition and is present in the source table only. Now when we use WHEN NOT MATCHED BY TARGET clause in the merge statement to insert the additional row from Locations_stage into Locations.

例如，表Locations_stage中LocationID = 4的行与连接条件不匹配，仅存在于源表中。现在，当我们在merge语句中使用WHEN NOT MATCHED BY TARGET子句将Locations_stage中的其他行插入到Locations中时。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
 
 
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName);

We can use additional search condition to filter the rows inserted into the target table. In this case, the rows which do not match with join condition but satisfy the additional search condition were only inserted into the target table.

我们可以使用其他搜索条件来过滤插入目标表中的行。在这种情况下，与连接条件不匹配但满足附加搜索条件的行仅插入到目标表中。

使用MERGE删除目标表中的行。 (Using MERGE to delete the rows in the target table.)

We can use WHEN NOT MATCHED BY SOURCE clause in SQL Server MERGE statement to delete the rows in the target table that does not match join condition with a source table.

我们可以在SQL Server MERGE语句中使用WHEN NOT MATCHED BY SOURCE子句来删除目标表中与源表的联接条件不匹配的行。

For example, the row with locationID =2 in the target table does not match the join condition and the row is present only in the target table. So, when we use WHEN NOT MATCHED BY SOURCE and can either delete the row or update it. Please refer to the below T-SQL script to delete the row in the target table using WHEN NOT MATCHED BY SOURCE clause.

例如，目标表中locationID = 2的行与联接条件不匹配，并且该行仅存在于目标表中。因此，当我们使用WHEN NOT MATCHED BY SOURCE时，可以删除该行或对其进行更新。请参考下面的T-SQL脚本，以使用WHEN NOT MATCHED BY SOURCE子句删除目标表中的行。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE;

We cannot use WHEN NOT MATCHED BY SOURCE clause more than two times. If WHEN NOT MATCHED BY SOURCE clause in SQL Server MERGE statement was specified two times, one must use an update operation and another one must use delete operation. Please refer to below T-SQL script for using WHEN NOT MATCHED BY SOURCE clause two times.

我们不能使用WHEN NOT MATCHED BY SOURCE子句两次以上。如果在SQL Server MERGE语句中两次指定了WHEN NOT MATCHED BY SOURCE子句，则一个必须使用更新操作，而另一个必须使用删除操作。请参考下面的T-SQL脚本两次使用WHEN NOT MATCHED BY SOURCE子句。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street'),(6,'James Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN NOT MATCHED BY SOURCE AND LocationID =2 
THEN 
DELETE
WHEN NOT MATCHED BY SOURCE AND LocationID =6
THEN UPDATE SET LocationName ='Test';

We can use all the three clauses in the single merge statement to synchronize the target table with the source table. Please refer to the sample T-SQL script to synchronize the target table with the source table using MERGE statement and all the three clauses.

我们可以在单个merge语句中使用所有三个子句，以使目标表与源表同步。请参考示例T-SQL脚本，以使用MERGE语句和所有这三个子句将目标表与源表同步。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
 
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE;

SQL Server merge example for all three clauses to synchronize table

MERGE中的TOP子句 (The TOP clause in MERGE)

A TOP clause in the MERGE statement is used to limit the number of rows affected. A TOP clause is applied after removing all the rows that do not qualify for the insert, update, and delete operations. Please refer to below T-SQL script for the SQL Server MERGE statement with the TOP clause.

MERGE语句中的TOP子句用于限制受影响的行数。在删除所有不适合插入，更新和删除操作的行之后，将应用TOP子句。请参阅下面的T-SQL脚本，以获取带有TOP子句SQL Server MERGE语句。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street'), ()
 
MERGE top (1) Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
 
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE;

We can see only the row with LocationID =1 is updated and the rows that were not matched by source are not deleted and the rows that were not matched by the target are not inserted.

我们可以看到只有LocationID = 1的行被更新，与源不匹配的行不会被删除，与目标不匹配的行也不会被插入。

MERGE中的OUTPUT子句 (OUTPUT clause in MERGE)

OUTPUT clause in the merge statement will return one row for each row that is modified in the target table. $action is used to know whether the row in the target table is deleted, inserted, or updated. Following T-SQL script is the example of the OUTPUT clause in the MERGE statement.

merge语句中的OUTPUT子句将为目标表中修改的每一行返回一行。 $ action用于了解目标表中的行是删除，插入还是更新。以下T-SQL脚本是MERGE语句中OUTPUT子句的示例。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
 
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE
  OUTPUT DELETED.*, $action AS [Action], INSERTED.* ;

Please refer to the below image for the output of the above script which shows action and the inserted and deleted data in SQL Server MERGE statement. you can also insert the output data in another table if you want to track or audit changes later.

请参考下图，以获取上述脚本的输出，该脚本显示操作以及SQL Server MERGE语句中插入和删除的数据。如果以后要跟踪或审核更改，也可以将输出数据插入另一个表中。

在带有触发器的表上使用MERGE (Using MERGE on a table with triggers )

If we are creating instead of triggers on the target table, we must create instead of triggers for all the DML actions specified in the MERGE statement. If we create only INSTEAD OF INSERT trigger on the target table and specify INSERT, DELETE AND UPDATE operation in the MERGE statement, it throws an error.

如果要在目标表上创建而不是触发器，则必须为MERGE语句中指定的所有DML操作创建而不是触发器。如果仅在目标表上创建INSTEAD OF INSERT触发器，并在MERGE语句中指定INSERT，DELETE和UPDATE操作，则会引发错误。

Following is the example T-SQL script with INSTEAD OF INSERT trigger on the target table and all three DML operations in the MERGE statement.

以下是示例T-SQL脚本，该脚本在目标表上具有INSTEAD OF INSERT触发器，在MERGE语句中具有所有三个DML操作。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
GO
 
CREATE TRIGGER TEMP ON Locations
INSTEAD OF INSERT
AS
BEGIN
PRINT 'Hello'
END
 
GO
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
 
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE;

SQL Server merge example with instead of triggers

Please refer to below example of instead of trigger for all actions specified in the merge statement.

对于合并语句中指定的所有操作，请参考以下示例，而不是触发器。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
GO
 
CREATE TRIGGER TEMP ON Locations
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
PRINT 'Hello'
END
GO
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
 
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE;

If you have any trigger on the target table, you cannot use the OUTPUT clause in SQL Server MERGE statement directly.

如果目标表上有任何触发器，则不能直接在SQL Server MERGE语句中使用OUTPUT子句。

SQL Server merge example with trigger and output clause

In this case, we must create a table and use INSERT INTO to insert the output data into the newly created table.

在这种情况下，我们必须创建一个表并使用INSERT INTO将输出数据插入到新创建的表中。

Please refer to the below example for the trigger on the target table and output clause in the MERGE statement.

请参考以下示例，了解MERGE语句中目标表和输出子句的触发器。

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations_stage')
BEGIN 
DROP TABLE Locations_stage
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
END
 
 
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 
 
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
) 
GO
 
 CREATE TABLE #Output  
    (DLocationID INT, 
     DLocationName VARCHAR(100),  
     ActionTaken nvarchar(10),  
     ILocationID INT,  
     ILocationName VARCHAR(100)  
     
    )
  GO
 
INSERT INTO Locations values (1,'Richmond Road'),(2,'Brigade Road') ,(3,'Houston Street')
 
 
INSERT INTO Locations_stage values (1,'Richmond Cross') ,(3,'Houston Street'), (4,'Canal Street')
GO
 
CREATE TRIGGER test_1 on Locations
FOR INSERT
AS
BEGIN
PRINT 'HELLO'
END
GO
 
 
 
MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
 
WHEN NOT MATCHED BY SOURCE 
THEN 
DELETE
  OUTPUT DELETED.*, $action AS [Action], INSERTED.* INTO #Output ;

Alternatively, there are different ways to sync the source and target table. Let us see an example by using a left outer join to insert, delete rows and inner join to update matched rows. But we must write three different statements for synchronizing the tables.

另外，还有多种同步源表和目标表的方法。让我们看一个示例，该示例使用左外部联接插入，删除行，而内部联接更新匹配的行。但是我们必须编写三个不同的语句来同步表。

Please refer to below T-SQL script which uses left outer join to delete rows present only in the target table.

请参考下面的T-SQL脚本，该脚本使用左外部联接删除仅存在于目标表中的行。

DELETE T FROM Locations T 
LEFT OUTER JOIN Locations_stage S on T.LocationID=S.LocationID
WHERE S.LocationID IS NULL

Use inner join to update rows that match the join condition.

使用内部联接更新符合联接条件的行。

update t set LocationName=s.LocationName from Locations t 
inner join Locations_stage s on t.LocationID=s.LocationID

To insert new rows which are present only in source table use left outer join as below.

要插入仅在源表中存在的新行，请使用左外部联接，如下所示。

INSERT INTO Locations(LocationID,LocationName)
select  t.LocationID,T.LocationName FROM Locations_stage T 
LEFT OUTER JOIN Locations S on T.LocationID=S.LocationID
WHERE S.LocationID IS NULL

注意事项 (A word of caution)

Please make sure you have proper indexes on both tables and join only the required columns so that you do not run into performance issues while synchronizing tables.

请确保在两个表上都具有正确的索引，并且仅连接所需的列，以免在同步表时不会遇到性能问题。

结论 (Conclusion)

We explored different aspects of SQL Server Merge Statement with several examples in this article. In case you have any question, please feel free to ask in the comment section below.

我们通过本文中的几个示例探讨了SQL Server Merge语句的不同方面。如有任何疑问，请随时在下面的评论部分中提问。

翻译自: https://www.sqlshack.com/sql-server-merge-statement-overview-and-examples/

merge语句

culuo4781

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
merge语句_SQL Server MERGE语句概述和示例

merge语句 In this article, we will review SQL Server MERGE statement, alternatives to MERGE statement, different clauses and examples of MERGE statement in SQL Server. 在本文中，我们将介绍SQL Server MERGE语句，...
复制链接

扫一扫