THEN INSERT (category_id, category_name, amount) Suppose we have two table sales.category and sales.category_staging that store the sales by product category. If you want to synchronize the target table with the data from the source table, then you will need to use this match condition to delete rows from the target table. NOT MATCHED BY SOURCE: these are the rows in the target table that does not match any rows in the source table.Note that NOT MATCHED is also known as NOT MATCHED BY TARGET. In this case, you need to add the rows from the source table to the target table.
In the diagram, they are shown as orange.
DATA MERGE DEFINITION UPDATE
For the matching rows, you need to update the rows columns in the target table with values from the source table. MATCHED: these are the rows that match the merge condition.Third, the merge_condition results in three states: MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE. Typically, you use the key columns either primary key or unique key for matching. It is similar to the join condition in the join clause. Second, the merge_condition determines how the rows from the source table are matched to the rows from the target table. The following shows the syntax of the MERGE statement:Ĭode language: SQL (Structured Query Language) ( sql )įirst, you specify the target table and the source table in the MERGE clause.
However, SQL Server provides the MERGE statement that allows you to perform three actions at the same time. If you use the INSERT, UPDATE, and DELETE 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. The following picture illustrates the source and target tables with the corresponding actions: insert, update, and delete: In this case, you need to update the rows in the target table with the values coming from the source table. However, these rows have different values in the non-key columns. The source table has some rows with the same keys as the rows in the target table.In this case, you need to delete rows from the target table. The target table has some rows that do not exist in the source table.In this case, you need to insert rows that are in the source table into the target table. The source table has some rows that do not exist in the target table.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.
DATA MERGE DEFINITION HOW TO
Summary: in this tutorial, you will learn how to use the SQL Server MERGE statement to update data in a table based on values matched from another table.
0 kommentar(er)
