merge语句_SQL Server MERGE语句概述和示例_culuo4781的博客

合并声明简介 ( 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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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
  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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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;
  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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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;
  使用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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
DELETE;
  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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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')
CREATE TRIGGER TEMP ON Locations
INSTEAD OF INSERT
BEGIN
PRINT 'Hello'
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
DELETE;
  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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
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')
CREATE TRIGGER TEMP ON Locations
INSTEAD OF INSERT, UPDATE, DELETE
BEGIN
PRINT 'Hello'
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
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子句。  
  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
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN 
DROP TABLE Locations
CREATE TABLE [dbo].[Locations](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
CREATE TABLE [dbo].[Locations_stage](
  [LocationID] [int] NULL,
  [LocationName] [varchar](100) NULL
 CREATE TABLE #Output  
    (DLocationID INT, 
     DLocationName VARCHAR(100),  
     ActionTaken nvarchar(10),  
     ILocationID INT,  
     ILocationName VARCHAR(100)  
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')
CREATE TRIGGER test_1 on Locations
FOR INSERT
BEGIN
PRINT 'HELLO'
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 
INSERT (LocationID,LocationName)
VALUES (S.LocationID,S.LocationName)
WHEN NOT MATCHED BY SOURCE 
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语句 
                    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 2008被引入，它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。”,通过这个描述，我们可以看出Merge是关于对于两个表之间的数据进行操作的。
 可以想象出，需要使用Merge的场景比如:
	 基于源表对目标表做I...
				最近学习了SQL Server这个牛逼轰轰的语法，个人认为很牛逼。
牛逼之处： MERGE语法可以将简单的INSERT，DELETE，UPDATE语句融合到一条语句中。
使用要领： MERGE语法必须作用于 两个数据源（数据表，子查询，视图） 之间
语句结构分析：
MERGE 目标表
USING  数据源（数据表，子查询，视图）
ON 匹配条件
WHEN MATCH THEN 操作语句 ；（结束的...
				用过sql server的Merge语句的开发人员都应该很清楚Merge用来做表数据的插入/更新是非常方便的，但是其中有一个问题值得关注，那就是Merge语句中的源表中不能出现重复的数据，我们举例来说明这个问题。
现在我们有一张表叫T_Class_A，其建表语句如下：
CREATE TABLE [dbo].[T_Class_A](
    [ID] [int] IDENT...
				一、使用背景    当需要对一个表根据不同条件分别进行INSERT、UPDATE以及DELETE操作时，可以使用MERGE(融合，合并)语句。MERGE语句可以根据不同条件获取要插入、更新或删除到表中的数据行，然后从1个或多个数据源头对表进行更新或者向表中插入行。二、MERGE语句的语法MERGE INTO 表名
USING 表名/视图/子查询 ON 连接条件
-- 当匹配得上连接条件时
WHEN...
using tableName2 on  joinCondition 
when   matched then update set …. 
when   not matched then insert  …..value ……tableName1:需要合并的目标表 
tableName2:需要
				在更新数据仓库时，经常需要根据源表对Target表进行数据同步，Merge 命令具有数据更新，删除，插入的功能，专门用于数据同步，并将数据的更新输出到表中。在使用Merge命令时，需要注意when not matche子句：
when not matched by target ：当Target Table不匹配时，数据行不存在于Target Table中，存在于Source Ta...
MERGE INTO 的用途 MERGE INTO 是Oracle 9i 以后才出现的新的功能。那这个功能 是什么呢? 简单来说,就是:“有则更新,无则插入”。
此语法在 Oracle、Sql Server、DB2中都可以使用。
二、merge into 语法
Oracle 中 merge into 用法解析:
merge into 语法：
merge into [target-table] a 
using [source-table sql] b on([conditional express
				Merge语句：
假设有两个表名为： source 表和 target 表，并且需要根据 source 表中匹配的值更新 target 表。 有 三种情况：source 表有一些 target 表中不存在的行。在这种情况下，需要将 source 表中的行插入到 target 表中。
target 表有一些 source 表中不存在的行。 在这种情况下，需要从 target 表中删除行。source 表的某些行具有与 target 表中的行相同的键。
但是，这些行在非键列中具有不同的值。在这种情况下，需要使用
				In this article, I am going to give a detailed explanation of how to use the SQL MERGE statement in SQL Server. The
    MERGE statement in SQL is a very popular clause that can handle inserts, ...
ON T.keyColumn = S.keyColumn
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2
WHEN NOT MATCHED THEN
    INSERT (keyColumn, column1, column2)
    VALUES (S.keyColumn, S.column1, S.column2); 
其中，targetTable是要更新或插入数据的表，sourceTable是提供数据的表，keyColumn是用于匹配两个表的列，column1和column2是要更新或插入的列。