SQL - Data Merge - Part 1 - Merging tables in SQL Server
|
Haziran 7, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Merging Data in SQL
- What is merging in SQL
- Merging two table to delete a row which has not corresponding entry in other table
- Using MERGE statement to update a table
Article covers following key subjects:
- SQL merge
- SQL merge using
- SQL merge statement
- SQL merge syntax
Articles tries to provide answer to following questions
- How to merge two tables in SQL server?
- How to check two table to clean unknown entries?
- How to delete rows which have not corresponding reference keys in other table?
- How to use MERGE command in SQL queries?
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 Delete
- Good knowledge of SQL OUTER JOINs
Merging two table in SQL Server
In this article, we will look into SMerging Tables and its basic form first and with next articles we will extend it slowly to understand how to use merge queries to update, delete or insert rows into a source table with comparing rows to another target table. Merging subject can lead confusion easily, therefore every article we will extend query slowly to pick up one feature of it.
Lets first talk about What is Merging and why we use it. In SQL, there will be scenarios that when one table has rows that reference in another table, and we will need to compare those two tables to execute update, insert or delete depending on a compare query. For instance: lets assume we have two table. Customers and orders table. Every customer can have an order in orders table.
We have 5 customer and 10 order. Randomly thinking, every customer few orders in orders table. The relationship between that two is one to many. However, lets assume that we have 1 order record but it does not belong to any client in customers table. customerID primary key in customers table, and customerID is foreign key in orders table. However, there is a row in orders that customerID does not exist in customers table. Instead one to many, it is some sort of situation that unknown to many relationship between two table.
To be practical, lets look scenario in an example from sql server.
Above sql scenario was an example which we used to cover sql delete subject with JOIN. Lets consider about tables for that article scenario. There were 6 customer row in our customers table. And with a reason we deleted the last row "zeta corporation" customer entry row. However, if you noticed in orders table, there is a row still that belongs that customer. Zeta Corp had customerID as 6. Orders table has an order entry - a row with customerID 6 still. Assume that there are many that kind of rows and broken data integrity in orders table. And we need to compare both customers table and orders, and need to tell SQL server that if there is not a customer match in orders table, we want to delete it.
We can archive that task with merging two tables. Merge will compare two tables according to a primary - foreign key relation or even not primary - foreign key: relational column on both. Further, merge will execute a delete, an update or an insert depending on match situation between two table. We will first cover how to use it its match condition.
Lets look at in a merge example to understand basic syntax and use of merge in SQL.
Example to Merging two tables to delete a row in match case
In following sql table merge example, we will use same tables above that we took over from a sql delete. Merge syntax is as follows.
MERGE targetTable1
USING sourceTable1
on sourceTable1.primaryKey = targetTable1.foreignKey
WHEN MATCHED
THEN UPDATE
SET targetTable1.columnX = valueX
In order to not cause confusion, we will use only one WHEN MATCHED situation between merging two table. Lets understand syntax first a bit before sql merge example.
- 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. WHEN MATCHED THEN UPDATE block is the case primary-foreign keys of two table match. We will use OUTER JOIN first to bring that two tables how it looks before using that MERGE match. Since it works on a JOIN similar to ON condition.
Lets see OUTER join result of two table:
As above indicates, both table has unreferenced entries. In that article, we will not work with that records, but one of main use of sql merge is for those records. We will cover its first part of MERGE, "WHEN MATCHED THEN UPDATE" part. We marked not referenced rows in above, with extending merge command we will also work on that records too in next 2 article.
For that article, first lets look at how to execute a update-set query in the match of primary-foreign key of two table and other given conditions in following merge query example on above tables.
As above sql table merge example indicates, a single value in a single row has been updated according to given value in set part of WHEN MATCHED THEN UPDATE.
In next article, we will extend use of MERGE to use other extended blocks.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|