SQL - Data Merge - Part 3 - Extending Merge to handle cases of target does match the source table in SQL Server
|
Kasım 6, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Brief explanation of Merging Tables in SQL Server
- Merging two table in situations that target table does not source table in merge stage
- Using MERGE statement and inserting rows due not matched by source table
Article covers following key subjects:
- SQL merge
- SQL merge using
- SQL merge statement
- SQL merge syntax
- SQL WHEN NOT MATCH BY SOURCE
- SQL WHEN NOT MATCHED BY TARGET
Articles tries to provide answer to following questions
- How to merge two tables in SQL server?
- How to check two tables and modify target table depending on a condition of source table?
- How to use WHEN NOT MATCHED BY TARGET with merge command?
- How does WHEN NOT MATCHED BY TARGET in a merge query work?
Article covers followings indirectly:
- Basic query flow in SQL Server
- SQL INSERT
- SQL OUTER JOIN
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of Basic query flow in SQL
- Basic knowledge of SQL INSERT command
- Basic knowledge of SQL UPDATE command
- Good knowledge of SQL FULL OUTER JOIN
- Basic knowledge of SQL Merge query
Merging two table in SQL Server
In this article, we will look into SMerging Tables in the case that target table does not match to source table, the case that target table have rows that do not match source when its compared with source table. Merging subject can lead confusion easily, therefore in earlier article we had covered this merge subject slowly part by part.
In part 1: we covered basics of merge query and WHEN MATCH block of sql merge query
In part 2: we covered basics of merge query again and WHEN NOT MATCHED BY SOURCE block of sql merge query
In this part 3, we will cover last block of merge query, WHEN NOT MATCHED BY TARGET.
In earlier article, we had used merge query to compare two table and had updated a value depending on a match. Following it, 2nd article, we had covered WHEN NOT MATCHED BY SOURCE. Merge query was using WHEN MATCH block to execute DML query. Lets recall it:
Above sql merge query was example of part 1 (WHEN MATCH block). Merge query had a condition and WHEN MATCHED block had been executed depending on merge condition. Merge query was saying to SQL Server, to match customerIDs in both table (primary-foreign key relation), and further cases was:
itemid=3
customerName = 'cisco'
quantity = 655
Lets recall some basics about merge again with above scenaro: orders table was target table and customers table was source table. Merge works on target table only (all update, insert or delete happen in target table: not the source table). Orders table was target. Variation of explanations in examples can be different. We used customers as source table, to just use customer name in condition to update orders table. We wanted to update orders table if cisco had an order with amount 655, to 11111.
WHEN NOT MATCHED BY TARGET in sql merge queries
Lets cover our main subject WHEN NOT MATCHED BY TARGET. To understand it easier, we need to compare it with WHEN NOT MATCHED BY SOURCE. In sql merge query, if source table does not match to target table (according to merge condition), WHEN NOT MATCHED BY SOURCE block and query following it is executed. WHEN NOT MATCHED BY TARGET is opposite. If target table does not match to source table, then WHEN NOT MATCHED BY TARGET is executed.
Lets look at in an example before discussing scenario of table. Since it can lead confusion easy and open to misunderstand which leads wrong sql updates and lose of data.
Example of WHEN NOT MATCHED BY TARGET in sql merge query
In following sql merge query example: we will use earlier example tables. Lets see our tables before going on sql merge and NOT MATCH BY TARGET.
Original tables:
In above:
We have customers table. CustomerID is primary key. Other table is orders table contains orders from that customers. CustomerID in orders table is foreign key. It is one to many relationship. There can be only one customer with same name, but can be many order from that customer in orders table.
We used an OUTER JOIN to bring both tables together. Orders table has been joined to customers. With that way, we can see which order has no customer and which customer has no order.According to JOIN result, there is 1 order that has no entry in customers table. customerID seems 6. But there is not such customer in customers table.
Lets merge that two table know to see WHEN NOT MATCHED BY TARGET. Customers table will be target. Orders table will be source. Merge condition will be primary key to foreign key assignment. That condition results with:
Orders table (source table) has a row, and customers table (target table) has not a matching row for it. There is missing row in target table. WHEN NOT MATCHED BY TARGET can insert a new row to target in that case.
Lets see how WHEN NOT MATCHED BY TARGET will work in sql merge query:
There was a row which says customerID is 6. This row did not match to target table (customers table in above). Therefore, insert worked and added a row. In insert statement, we provided customerID manually (to make example easier, we did not make customerID primary key, in many merge case it will be more complex cases). Further, we added some text info to customerName and customerInfo columns according to row info in orders table.
In different sql merge examples, different sql merge examples with more complex queries will be explained in different articles.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|