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
- Assistance with MSSQL query to group data
- SQL Server 2008 Question
- Calculation Error in SSAS VS 2005
- How to create a PDF database diagram in SQL Server?
- SQL Server insert values floating point value is out of range
- Connect to MS SQL Server on Arch Linux
- SQL Server - Auto-incrementation that allows UPDATE statements
- How to get tag attribute value from xml
- How to use mssql_conn_id in Airflow MsSqlOperator?
- How to properly return only MAX values for specific/each columns
- Update data in different tables in SQL Server
- SQL string or binary data would be truncated , column name
- Proper use of SqlDataReader
- jsp sql connetion sockect error mssql server2008R2
- SSIS package runs fine manually but throws error via JOB
- Calculate the rank and apply it following an order
- one to many relation between table columns. Grouping and finding combinations
- SQL Query with Dynamic Columns Using Pivot
- SQL Server INSERT syntax
- Powershell sql server module 2017 manual install
- Why getting second highest list product from this query is not working
- Avoid duplicate insert to database
- SQL - Group by Filter
- How to calculate the total Hours
- Tools to Replay Load on a SQL Server
- How to design a database for User Defined Fields(UDF)?
- SQL string not line breaking in gridview
- Using SELECT against a linked server database with ApplicationIntent=ReadOnly turned on
- SQL won't insert null values with BULK INSERT
- SQL Server database design, which one is better design
- Where can i find the: 1) cache hits and 2) cache lookup stats in SQL Server
- SSIS tasks are getting fail with Communication link failure error
- Multiple nested queries to count total of each category
- SQL Server BULK Insert with non standard NULL value
- Testcafe - How to run code after all the fixtures are run
- how to compare the fied password in master.dbo.syslogins in ms sql 2008
- TSQL how do you iterate through rows while parsing them?
- T-SQL command to get only rows that can CAST()
- Add data to mssql string to byte error c#
- Want to pull variable info from a Comments section
- Check if one of many temporary table exists and have values in SQL Server 2014
- SQL Server : slow query with 3 "OR" condition and order by Id
- Get SQL Table Info
- SQL Server TCP/IP times out
- get a count of each value from every column in a table SQL Server
- Petapoco execute stored procedure without waiting
- SQLSTATE[01002] Adaptive Server connection failed (severity 9) error on Ubuntu Linux VPS
- How to extract data from this table and convert it into this specific format using t-sql?
- Merge multiple rows into one column without duplicates
- C# simple code to write an INSERT query is giving an exception
- Join two tables. Select all rows from one table and only matching values from other table?
- Comma Separated String from Junction Table Inner Query (SQL Server)
- Include a blank row in query results
- SQL Exclude rows previously selected in another category
- Select specific row but based on another column role
- Help with SQl Server and XML?
- Update a row multiple times when performing a join
- Search parent-child relationship until parent meets a condition
- Why is not my stored procedure executing CLR method with some id's?
- How to return a field as Nullable from a View
- How to display all three different numbers(Work, Home, Cell) for client in on column over the rows?
- Pivot only only value and select the remaining without pivot
- VB.NET - Failed to convert parameter value from a String to a Guid issue
- How to connect with SLIM REST api with MS SQL server?
- Get average of TIME(7)
- How to insert a time-zoned converted Timestamp into a table?
- Convert XML to SQL Server table
- SQL Server : Replicate Schema changes to another database
- How to search with parameter in MVC5 without ef
- SQL Server Populate XML Variable from VARCHAR rows
- Invalid object name 'product_Design'
- Split and save in SQL Server
- Conditional auto increment value in SQL server "SELECT"
- How to tell the data types after executing a stored procedure?
- SqlDataAdapter.FillSchema() method doesn't work
- Two joins with PIVOT
- Same Long string(Varchar(max)) get different hash value
- Select Previous x Quarter dates
- SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security
- How do I update SSIS package on the server
- How to Sort Ascending Without "ORDER BY " in SQL
- Finding all views that are using linked server
- Count of Instances for One Attribute Based on Several Other Attributes
- SQL Insert Query With Condition
- SSIS - OLEDB Source - how to use sql query with variables?
- How to convert SQL Server database to MySQL database
- Entity Framework : Create a model from Dictionary<TKey,TValue> to be mapped to a database table
- Reporting Services 2005 Timeout When Exporting via Web Service
- Database deployment inadvertently dropping SQL Broker objects in database if the user is not a sysadmin
- Using a case to assign numerical values and pull through the highest value
- SQL Server : verify that two columns are in same sort order
- Unable to access imported data on Cloud SQL with SQL Server database
- ExecuteScalar can not find stored procedure
- Convert DateTime to US and European
- Move database from SQL Server 2008 Express to SQL Server 2012
- SQL - How to compute percentage and add to query result
- SQL Server 2012 join query
- How to find gaps in dates in SQL Server?
- Which is the smallest datatype to store an integer value in SQL Server 2008?
- Importing Excel Data Seems to Randomly Give Null Values
- EF to POCO - Oracle or SQL Server - Numeric Mapping/Conversion
- Displaying data in a different manner
- Native queries support Spring data jpa stream for SQL Server
- Export data from SQL and write to etxt file (No BCP or SP can be used)
- Cumulative Sum - Choosing Portions of Hierarchy
- SQL Server VB .NET (or C#) datetime Canonical
- Using an aggregate for totals using Rollup
- SQL Server Violation of PRIMARY KEY constraint 'PK
- Accessing derived tables from outer query
- update columns values with column of another table based on condition
- Why is my SQL UPDATE syntax giving 'ambiguous column name'?
- red herring error "The user does not have permission to perform this action"
- SQL: What's the purpose/benefit of joining on multiple conditions?
- Update every row with data from row above
- Select column name dynamically based on rows from another table in SQL server
- Creating a Trigger to update
- Fetching MAX id from SQL Server database causes exception
- SqlParameter not working with INSERT statement
- Floor a date in SQL server
- Finding records in another table
- Varchar value split in SQL
- T-SQL - Convert datetime to unseparated ISO value
- SQL UNION with ORDER
- How to use pivot in a recursive stored procedure
- Proper Index Order
- How to merge two rows by updating only NULL values in TSQL?
- Return value from stored procedure to c#
- Group by month taking into account on timezone (SQL Server)
- using if else in sql server subquery
- SQL Server dynamic queries
- SQL Server Case Statement
- Report Builder 3.0 Add Data Elements to an Array
- How can I capture the print message from a SQL Server stored procedure?
- SQL Server Stored Proc - What is returned to a variable if no data?
- Cancel background worker which is calling external process
- Update all records - no condition
- Retry policy in Entity Framework
- Hibernate, proper way to modify multiple tables in one transaction
- SQL Server Date Range Query Performance
- Getting an error when trying to compare two dates
- SQL Conditional relationship between 1 parent 3 child tables
- Need help creating a view
- MsSql Compare specific datetimes in sequence based on ID
- SSIS Project validation fails if ServerName is parameterized but ConnectionString is not
- Limit SQL query to one on one join
- Assign a unique count to each row in the resulting table of a select statement
- Unable to insert record from user-defined into Table
- Count current job with a partition
- Suggestions for SQL Server GUI?
- Import EventViewer error logs to sql server table using Powershell or SSIS