SQL - Data Merge - Part 4 - Extending Merge to handle cases that the primary keys in target table does not match up a foreign key in source table.
|
Aralık 6, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Brief explanation of Merging two table in SQL Server
- Merging two tables in situations that source table does not match merge condition
- Using SQL MERGE query to delete rows from target table when there are unmatch in source table side
- Deleting primary key rows from target table when they have not foreign key entries in JOIN query result
Article covers following SQL key subjects:
- SQL merge
- SQL merge statement
- SQL merge syntax
- SQL WHEN NOT MATCHED BY SOURCE THEN DELETE
Article tries to provide answer to following questions
- How to merge two tables in SQL server?
- How to compare to tables to delete rows that does not reference to any foreign key?
- How to join two tables to delete missing primary keys in one to many relationships?
- How to delete rows which have not corresponding reference keys in other table?
- How to use WHEN NOT MATCHED BY SOURCE to delete a row in SQL merge query?
Article covers followings indirectly:
- Basic query flow in SQL Server
- SQL DELETE
- 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 query
- Good knowledge of SQL FULL OUTER JOIN
- Basic knowledge of SQL Merge query
- Basic knowledge of WHEN NOT MATCHED BY SOURCE
In this article, we will look into SMerging two Tables in the case that target table has primary keys which do not reference to any foreign keys in another table (in the source table. The case that target table have rows that do not match source when its compared with source table.
In earlier articles, we had used SQL merge query to compare two tables and had updated a value depending on a match. In that SQL merge example, we will delete a row in a unmatch scenario.
Lets check example tables and a JOIN result for a scenario that we will use SQL merge and delete.
In above SQL tables and join result, we have two tables which are customers table and orders table. CustomerID in customers table is primary key and customerID is foreign key in orders table. Tables have a relation type of one to many. There can be one customer with same name in customers table, but it can have many orders in orders table. If you check orders table, two customers have not any order there. IBM and Microsoft seems did not give any order. ID of 4 and 5 does not exist in orders table.
We assume that we want to use SQL merge to merge that two table to compare and delete primary key entries that do not exist as foreign key in other table. More specific, we want to remove customers from customers table which have not any order in orders table. Some sort of cleaning a table according to another table.
We will use SQL merge in that case to compare two tables to delete some rows. Lets cover how to merge that two table and delete rows from a table according to result of comparison with other.
WHEN NOT MATCHED BY SOURCE in SQL merge queries
Lets cover WHEN NOT MATCHED BY SOURCE briefly again here. When SQL merge condition compares two tables, source table has foreign key entries and target has not primary key that matches up: that WHEN NOT MATCHED BY SOURCE is executed in SQL merge. In our scenario: customers will be target, orders will be source table in SQL merge query. Source table orders have foreign keys, target customers table have not primary key entries that match to that foreign keys. In practice, target table customers have customers which have not any order in source orders table. It means merge does not match in source side.
We can see how to use delete with WHEN NOT MATCHED BY SOURCE in SQL merge example after that explanation.
Example of WHEN NOT MATCHED BY SOURCE THEN DELETE in SQL merge query
In following SQL merge example, we will use SQL merge with WHEN NOT MATCHED BY SOURCE and DELETE with it. In above tables SQL query image, as step 1 and step 2 we saw our tables and a SQL join result of those tables. Join result indicates that two customers have no order. We will delete them with SQL merge.
SQL Merge and delete example:
As above SQL merge query result indicates, SQL merge query compared two tables. Target table customers had two customer record that source table orders does not contain any order from them. It was WHEN NOT MATCHED BY SOURCE (non technically to speak: merge condition had no match in source table side: 2 row). Delete keyword following THEN keyword deleted that rows from target table (customers).
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|