{"id":724,"date":"2018-07-31T09:32:50","date_gmt":"2018-07-31T02:32:50","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=724"},"modified":"2020-04-11T20:13:17","modified_gmt":"2020-04-11T13:13:17","slug":"sql-server-merge","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-merge\/","title":{"rendered":"SQL Server MERGE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server MERGE<\/code> statement to update data in a table based on values matched from another table.<\/p>\n\n\n\n

Introduction SQL Server MERGE Statement #<\/a><\/h2>\n\n\n\n

Suppose, you have two table called source and target tables, and you need to update the target table based on the values matched from the source table. There are three cases:<\/p>\n\n\n\n

  1. The source table has some rows that do not exist in the target table. In this case, you need to insert<\/a> rows that are in the source table into the target table.<\/li>
  2. The target table has some rows that do not exist in the source table. In this case, you need to delete<\/a> rows from the target table.<\/li>
  3. The source table has some rows with the same keys as the rows in the target table. However, these rows have different values in the non-key columns. In this case, you need to update<\/a> the rows in the target table with the values coming from the source table.<\/li><\/ol>\n\n\n\n

    The following picture illustrates the source and target tables with the corresponding actions: insert, update, and delete:<\/p>\n\n\n\n

    \"SQL<\/figure>\n\n\n\n

    If you use the INSERT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code> statement individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table.<\/p>\n\n\n\n

    However, SQL Server provides the MERGE<\/code> statement that allows you to perform three actions at the same time. The following shows the syntax of the MERGE<\/code> statement:<\/p>\n\n\n

    MERGE<\/span> target_table USING<\/span> source_table\nON<\/span> merge_condition\nWHEN<\/span> MATCHED<\/span>\n    THEN<\/span> update_statement\nWHEN<\/span> NOT<\/span> MATCHED<\/span>\n    THEN<\/span> insert_statement\nWHEN<\/span> NOT<\/span> MATCHED<\/span> BY<\/span> SOURCE<\/span>\n    THEN<\/span> DELETE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    First, you specify the target table and the source table in the MERGE<\/code> clause.<\/p>\n\n\n\n
    Third, the merge_condition<\/code> results in three states: MATCHED<\/code>, NOT MATCHED<\/code>, and NOT MATCHED BY SOURCE<\/code>.<\/p>\n\n\n\n
    Second, the merge_condition<\/code> determines how the rows from the source table are matched to the rows from the target table. It is similar to the join condition in the join<\/a> clause. Typically, you use the key columns either primary key<\/a> or unique key<\/a> for matching.<\/p>\n\n\n\n