SQL - Data Merge - Part 2 - Extending Merge to handle cases of source table does match the target table
|
Kasım 6, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Brief explanation of Merging Data in SQL
- Merging two table in situations that source table does not match merge condition
- 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 MATCHED BY SOURCE
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 delete rows which have not corresponding reference keys in other table?
- How to use WHEN NOT MATCHED BY SOURCE with merge command?
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
Merging two table in SQL Server
In this article, we will look into SMerging Tables in the case that source table has non-matching condition when its compared with target table. Merging subject can lead confusion easily, therefore in earlier article we had covered situation for WHEN MATCH situation, the first part block of query. In this article, we will extend query to pick up second block named WHEN NOT MATCHED BY SOURCE .
In earlier article, we had used merge query to compare two table and had updated a value depending on a match. Merge query was using WHEN MATCH block to execute DML query. Lets recall it:
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 as:
itemid=3
customerName = 'cisco'
quantity = 655
Its essential to understand that condition and target - source table logic for understanding WHEN NOT MATCHED BY SOURCE block that we cover in that article. Lets understand how condition works and where target and source table fits in merge query.
After merge our target table comes: orders. Target table means, when merging two table in sql, it is the table that will have modification like an insert, an update or a delete. After USING word source table is added. Source table in merge command serves as a source only. It does not get modification. That is essential to understand. Therefore, whatever merge condition we supply in sql query of merge, WHEN MATCH, WHEN NOT MATCHED BY SOURCE (this article we cover this), WHEN NOT MATCHED BY TARGET (next article we will cover that one too) their executions work on Target Table, not the Source Table. In our article, in the example, we will see all modifications happen to Target Table.
Merging two table and using WHEN NOT MATCHED BY SOURCE situation
In above sql query example, merge condition were addressing one row and a value from quantity column. WHEN MATCHED block executed given command according to match. Now lets look at WHEN NOT MATCHED BY SOURCE. This part means, execute a command when merge condition reason is Target table. This is essential point. BY source does not mean target table does not match source table. BY SOURCE means the rows which are out of condition in merge query that are in Target Table. Which means: if we provide a update command after WHEN NOT MATCHED BY SOURCE, it will update rows in orders table except the merge condition filters. To not be confusing lets see in example:
Our sql table merge query syntax with WHEN NOT MATCHED BY SOURCE is as follows:
MERGE targetTable1
USING sourceTable1
on sourceTable1.primaryKey = targetTable1.foreignKey
WHEN MATCHED
THEN UPDATE
SET targetTable1.columnX = valueX
WHEN NOT MATCHED BY SOURCE
THEN UPDATE
SET targetTable1.columnX = valueX;
To recall again the sql table meanings in that sql query
- Target Table means it is the target table that sql match will execute update query on its columns.
- Source Table means it is the source - reference column that we use to modify target table according to it.
In our scenario, orders table becomes target and customers table becomes source again in following example.
Lets add WHEN NOT MATCHED BY SOURCE to our earlier query too, to extend our sql table merge query.
As above indicates, two table has been compared with merge. Two seperate update has been executed on target table. In next article, we will take 3rd part of merge, WHEN NOT MATCHED BY SOURCE. Further we will extend our use of merge to deal with primary-foreign key does not match issues.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|