In this post will discuss the merge statement in the SQL server. So what is the use of the Merge statement? Merge statement, introduced in SQL 2008, allows us to perform insert updates and delete in one statement.
we can say that we don’t have to write multiple SQL scripts for performing, Crud operation or insert update, and delete with the help of Merge statement.
We have required two tables a source table and a target table. The source table is the table that contains the changes that need to be applied to the target table
The target table is the table that requires changes. It is the table upon which we will be performing insert updates and delete.
Using the Merge statement we join the target table and source table by using a common column between both the tables. Based on how the rows match up, we can then perform an insert update or delete on the target table.
Let’s look at an example now.
Here we got TblCustomerSource and TblCustomerTarget tables. TblCustomerTarget is the table on which we will be performing insert update and delete all in one statement.
We will be using the ID column in both the tables to join them.
Now, when the rows match between the tables, we want to perform an update on the target table.
If we look at the data that we have at the moment in both the tables we have a record with ID equals 2.
So those rows match and we want to do an update, meaning we want to update Dr. Jacob To Jacob. So when rows match, we want to perform an update.
When Rows do not match.
That is when the rows are present in the TblCustomerSource but not in the target table. We want to insert all such rows into the target table at the moment.
Mike’s record with ID=1 is present only in the source table. It’s not present in the target table. So we want to insert that record into the target table.
And finally, when the rows are present only in the target table, but not in the source table.
we want to delete all such rows from the target table at the moment, within the target table, we have Johnson record with Id=3. It’s not present in the source table, so we want to delete it from the target table.
So after we execute the merge statement, this is how we want the output to be
Notice Dr. Jacob Is updated to Jacob.
Mike’s record which is present only in the source table but not in the target table. It’s now inserted into the target table.
Johnson record, which is present only in the target table but not in the source table, is deleted from the target table, and here is the syntax to do it.
MERGE [dbo].[TblCustomerTarget] AS T USING [dbo].[TblCustomerSource] AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.CustomerName = S.CustomerName WHEN NOT MATCHED BY TARGET THEN INSERT (ID, CustomerName) VALUES(S.ID, S.CustomerName) WHEN NOT MATCHED BY SOURCE THEN DELETE;
So merge with specifying the target table. So in our case, the target table is [dbo].[TblCustomerTarget] ‘As’ and we give it an alias T and then using the source table.
So in our case, the source table is [dbo].[TblCustomerSource] And we give it alias ‘S’ on specified a join condition.
when the rows match based on that condition, then we want to perform an update on the target table. So we are saying UPDATE SET T.CustomerName = S.CustomerName. when the rows match, perform an update.
When not matched by Target. This means when the rows are present in the source table but not in the target table, then we want to do an insert. So we have an insert statement.
INSERT (ID, CustomerName) VALUES(S.ID, S.CustomerName)
when not matched by the source. This means when the rows are present in the target table but not in the source table, then we want to perform a delete, so pretty straightforward syntax there.
DELETE;
Merge statement must end with a ‘,’,
Table Script
/****** Object: Table [dbo].[TblCustomerSource] Script Date: 12/07/2020 6:00:18 PM ******/ GO CREATE TABLE [dbo].[TblCustomerSource]( [Id] [int] NOT NULL, [CustomerName] [nvarchar](50) NOT NULL, [Gender] [nvarchar](50) NULL, CONSTRAINT [PK_TblCustomerSource] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO /****** Object: Table [dbo].[TblCustomerTarget] Script Date: 12/07/2020 6:00:18 PM ******/ GO CREATE TABLE [dbo].[TblCustomerTarget]( [Id] [int] NOT NULL, [CustomerName] [nvarchar](50) NULL, [Gender] [nvarchar](50) NULL, CONSTRAINT [PK_TblCustomerTarget] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (1, N'Mike', N'Male') GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (2, N'Jacob', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (2, N'Dr. Jacob', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (3, N'Johnson', N'Male') GO
The post How to use merge statement in the SQL server appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Difference between SQL Server 2005 & 2008
- Functionality to save and restore database state
- Loading Queries in MS SQL Server Management Studio
- ORDER BY when using value gotten from sql function
- Split Embedded CSV Text Into Separate Rows
- Is there a difference in the underlying protocol for ODBC, OLEDB & ADO.NET
- NULL values in SQL server query
- SQL Function not working the way I want
- How to Query SQL Server ReportServer to find Parent Child Reports
- How to figure out weekly start date
- How do I find the root of a hierarchy using LINQ and EF?
- SQL Parent Child Tree Sort BY DEPTH and Display Order
- insert then select scope_identity() generates duplicate rows
- c# time to sql date time
- Multiple filters, aggregation and grouping in the same column
- Importing tab-delimited text file - can't change the column mappings SQL Server 2008
- Calling CTE multiple times in same query
- T-SQL Select statement returns zero rows when using IN
- Sql server complaining about this IF NOT EXISTS statement
- Connecting to sql server database mdf file without installing sql server on client machine?
- return count of likes and dislikes on image
- connecting to multiple databases using different jdbc drivers
- A string is saved every 20 seconds; is it faster to save it into a database or a text file?
- Separating substrings from space separated values
- What do I gain by adding a timestamp column called recordversion to a table in ms-sql?
- Full Text Not Returning The Expected Results
- Snapshot isolation transaction aborted due to update conflict in SQL Server due to FK checks - Part 2
- How do I insert and retrieve geography data in asp.net mvc connected with Entity Framework and using dapper?
- How to restrict the values read from a table based upon another table in sql
- Find all occurrences of sp_send_dbmail in a sql server 2008R2 database
- How to show blank record in sql if duplicate rows exists
- googleglass/mirror-quickstart-dotnet project gives SQL server error
- MS SQL Specific Tables Hanging at Queries
- Getting the Avg Age with multiple Parameters
- How to identify the version number of SQL Server 2008 Standard
- Executing SQL statement stored as a field value
- How to profile and address insert/update performance issues?
- Is this way to execute procedure is safe from SQL Injection?
- How to find factorial for given number without using * symbol in T-SQL query?
- How to create a 'sanitized' copy of our SQL Server database?
- sqlsrv_query() expects parameter 1
- SqlService WMI query in PowerShell returns nothing
- How to save file in SQL Server database if have file path?
- How to find Computed field in a table
- Can FireDac connect to SQL Server without ODBC?
- Performance issue while reading data from hive using python
- Allow SQL Server Remote Connection
- Is it good practice to keep Cursors in Try catch block?
- Case insensitive search in Microsoft SQL Server
- Return count in Stored Procedure
- How to find and update columns containing a superscript character
- Insert values or ignore row if a constraint fails
- Select a comma-separated value from one table and use it in where condition in another table using Functions in SQL Server
- Most efficient way to select the max value of a column and the corresponding data for that row
- sql server - For XML Raw - Add custom attribute to the element
- Tables with no Primary Key
- Working with datetime with dynamic query in SQL Server
- Enable constraint
- Fastest execution time for querying on Big size table
- Closing SQL-connection but open connections keeps incrementing
- Hierarchical SQL Query WITH parent child items
- Forward Engineering to SQL Server from MySQL workbench?
- sql - Find combinations of a column's string value
- SSAS dependent FormatString to display different unit of values
- How can I achieve SQL Pivot statement from LINQ
- Conversion to Time 12 hour Format From String containing Time in 24 hour format
- Laravel: PDO Exception, cannot find driver even though the driver is installed and tested
- Encrypting filestream data
- How to dispatch thousands of SQL requests asynchronously
- SQL Declare Variables
- sql server read xml without inner tags
- How can I determine whether Stored Procedure Parameter is optional from C# code (I am using Sql Server)
- MS SQL Server Zero Padding
- asp.net mvc - creating complex view based on stored procedure
- Use split function to show each value in seperate row
- SQL Server time stamp column insertion or updation possible explicitly?
- Implode type function in SQL Server 2000?
- How to convert MMYYYY (042011) to date and find datediff for todays date
- Group value via range and value sql query
- Binding for custom control works in GridView but not in FormView
- Is there an sql condition that can look for non integers in a column?
- Poor performance from looping SQL Server Update statement
- Simple select with trick
- foreach on selected rows SQL stored procedure
- T-SQL return rows with unique field
- Random Phone number generator
- Proper parameterized query class for asp.net - c# application - sql server
- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. SQL Server
- SQL Split - insert into hierarchical table structure
- SQL Query with join not updating column value
- Specified cast is not valid when I try to cast to (int?)
- ASP .NET: SQL Server Money Type and .NET Currency Type
- SQL 2008 blob data unused space cannot be freed
- Complex SQL WHERE clause issues
- How can I schedule a SQL job in Microsoft Azure SQL Database?
- SQL Union with "Ranks"
- How to mirror production SQL Server database with Local with ability to change local database for a period of time?
- SQL Server Management Studio (SSMS) setup fail (0x8007046)
- How to fast fill MS Sql 2005 wit data in c#
- Where can I find out the differences between SQL Server and SQL Azure?
- How to search a date field using wild cards?
- Concise way to count where two columns are non-null (in select clause)
- How to apply an index to a faked table in tSQLt
- sql server enforce constraint without a relation
- Can a BCP version 8.0 format file have a column name that is a SQL keyword?
- Matching two values in a table to another value in a table sql
- Shared SqlConnection
- How to get contain letter in c# like in sql
- Foreign Key '... ' references invalid column '...' in referencing table '... '. SQL Server
- SQL Function: convert variable from varchar into tinyint
- Calculating next letter in SQL
- make server name as a variable
- Change all empty strings to NULL using one query with loop. SQL
- How to update last n records from SQL Server
- EXEC dbo.sp_executesql @statement
- Changing a SQL Server Partition Date Boundary & Minimizing Data Movement
- Sql Server Network Configuration Protocols Not Available
- How can I automate my workflow of transferring data?
- Select top 1000 rows in MySQL Workbench And Generate List of Headers
- integer to date conversion issue in SQL vs Excel
- How would I combine 2 distinct tables into 1 table in SSIS?
- Need help making my stored procedure more efficient
- Save the result of an SQL function to a variable within a select statement?
- MSSQL 2008 SP pagination and count number of total records
- Is there an ASP.NET data type equivalent to the XML data type in SQL Server?
- T-SQL :: generate random Swiss National Identification Number (AHV/AVS)
- Inner join using two fields: use one or two indexes?
- How to UNION sub-query in T-SQL
- Stored procedure's output parameters in SQL Server
- Simplest way to process multiple hardcoded values in SQL?
- Validate varchar datatype value to format HH:MM
- SqlBulkCopy inserts when inside a transaction prevents any other writes on a table
- How subtract numerical ranges in SQL?
- Returning a list from an SqlCommand object - how can I give the database as little work as possible?
- Sort Grants by expiration date
- how to correct hresult error.ms sql server
- How do I version a SQL Server Database?
- Culture with money SQL
- Show row data in columns side by side in SQL Server
- SQL - How to query temporary table from stored procedure?
- XML Bulk Load issue into SQL Server
- SQL Updating records based on most recent date
- merging two left join on same table into one
- How to tell if SQL Server automated upgrade is successful from C# WinForms app
- How to do a cross-database-query join in SQLAlchemy?
- Check if Database exists MSSQL C#
- SQL Server generating XML with generic field elements
- How can I look for unique values while having duplicate ids?
- T-SQL - Non optimal plan is used - WHERE clause should be shortcircuited
- Inserting multiple rows into some table using values from select statement