Introduction to SQL Server MERGE statement
SQL Server is a relational database management system used by many organizations for storing, managing, and manipulating data. One of the many SQL Server statements available to users is the MERGE statement.
This statement serves the purpose of updating data in a table based on values in another table or a subquery. The MERGE statement is useful when you want to update data in the target table based on data from the source table.
Three cases of table data update
Before delving into the syntax of the SQL Server MERGE statement, it’s important to first identify the three cases of table data update. The first case is when you update an existing record in the target table with new data from the source table.
The second case is when you add a new record to the target table based on information from the source table. Finally, the third case is when you delete a row from the target table that has no equivalent in the source table.
Syntax of SQL Server MERGE statement
Components of MERGE statement
The syntax of the SQL Server MERGE statement consists of several components. The first is the target table, which is the table you want to update.
The next component is the source table, which is the table you want to use to update the target table. The final component is the merge_condition, which is the condition that determines how the data from the source table is merged with the data in the target table.
States resulting from merge_condition
The merge_condition is made up of two parts; the MATCHED and NOT MATCHED states. The MATCHED state is used to update existing data in the target table based on data from the source table.
The NOT MATCHED state is used to insert new data into the target table that is not in the source table. The NOT MATCHED BY SOURCE state is used to delete data from the target table that has no equivalent in the source table.
Let’s break down the syntax of the SQL Server MERGE statement:
“`
MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.PrimaryKey = S.PrimaryKey
WHEN MATCHED THEN
UPDATE SET T.FirstName = S.FirstName, T.LastName = S.LastName
WHEN NOT MATCHED THEN
INSERT (PrimaryKey, FirstName, LastName)
VALUES (S.PrimaryKey, S.FirstName, S.LastName)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
“`
In the above example, we are updating the target table, TargetTable, with data from the source table, SourceTable. We are using the common Key, PrimaryKey, to match records between the two tables.
For the MATCHED state, we update the FirstName and LastName columns of the target table with data from the source table. For the NOT MATCHED state, we insert a new record into the target table using data from the source table.
Finally, the NOT MATCHED BY SOURCE state deletes any records from the target table that do not exist in the source table.
Conclusion
The SQL Server MERGE statement provides a powerful tool for updating and manipulating data in tables. Learning the syntax and understanding the three cases of table data update will help you utilize this statement to its full potential.
Use the MERGE statement in your SQL Server statements to simplify your database management.
Example of SQL Server MERGE statement use
One of the best ways to understand the SQL Server MERGE statement is by implementing it with examples. In this article, we will use two tables, sales.category and sales.category_staging, to demonstrate how the MERGE statement can be used to update, insert and delete data in tables.
Tables used in example
The sales.category table is a table that contains information about product categories, while the sales.category_staging table is a staging table that is used to batch load category updates. The two tables have the same columns and primary keys, but the category_staging table contains temporary data that is intended to update the category table.
Implementation of MERGE statement
We can use the MERGE statement to update the category table with information from the category_staging table. Let’s see how this can be done below.
“`
MERGE sales.category AS target
USING sales.category_staging AS source
ON (target.category_id = source.category_id)
WHEN MATCHED THEN
UPDATE SET target.category_name = source.category_name
WHEN NOT MATCHED THEN
INSERT (category_id, category_name)
VALUES (source.category_id, source.category_name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
“`
In this SQL Server MERGE statement, we start by defining the target table, sales.category, and using the USING statement to define the source table, sales.category_staging. We then join the two tables on their primary key, `category_id`, using the ON statement.
The MATCHED keyword is used when a record in the target table matches a record in the source table based on the merge_condition. In this case, we update the target table with the category_name from the source table by using the UPDATE SET statement.
If the target table does not have a matching record in the source table, the NOT MATCHED condition is triggered. In this case, we insert the missing category_id and category_name using the INSERT statement.
Finally, the NOT MATCHED BY SOURCE condition is encountered if there is a record in the target table that does not have a matching record in the source table. In this case, we delete the unmatched record from the target table using the DELETE statement.
This MERGE statement is useful for situations where you need to batch update records in a table using a temporary staging table. Rather than using multiple SQL statements to add, update or delete data in the sales.category table, the MERGE statement simplifies the SQL code and streamlines the process.
Conclusion
In conclusion, the SQL Server MERGE statement is a powerful tool for updating and managing data in relational databases. With the ability to update, insert, or delete data from tables, the MERGE statement can be used to simplify many database management tasks.
By utilizing examples such as updating the sales.category table with information from a staging table, a better understanding of the MERGE statement can be acquired. So start using the SQL Server MERGE statement in your SQL codes and manage your data with ease.
In summary, the SQL Server MERGE statement is a useful tool that can update and manage relational databases with ease. It allows for updating, insertion, and deletion of data from tables, streamlining the database management process.
Understanding the syntax and the three cases of table data update is crucial to utilizing the MERGE statement to its full potential. With the example of updating the sales.category table with information from a staging table, we can see the MERGE statement in action.
Implementing the MERGE statement in SQL codes can simplify database management, making it an important topic worth mastering for data professionals.