In SQL Server 2008, you can perform multiple data manipulation language (DML) operations in a single statement by using the MERGE statement. For example, you may need to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Typically, this is done by executing a stored procedure or batch that contains individual INSERT, UPDATE, and DELETE statements. However, this means that the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. However, performance gains depend on having correct indexes, joins, and other considerations in place
USE [MyDatabase]
GO
merge into mytable as Target
using mytable2 as Source
on Target.id=Source.id
when matched then
update set Target.name=Source.name,
Target.Salary = Source.Salary
when not matched then
insert (id,name,salary) values (Source.id,Source.name,Source.Salary);
Bulk Load Best Practices
The MERGE statement can be used to efficiently bulk load data from a source data file into a target table by specifying the OPENROWSET(BULK…) clause as the table source. By doing so, the entire file is processed in a single batch.
To improve the performance of the bulk merge process, we recommend the following guidelines:
Create a clustered index on the join columns in the target table.
Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.
By default, the bulk operation assumes the data file is unordered. Therefore, it is important that the source data is sorted according to the clustered index on the target table and that the ORDER hint is used to indicate the order so that the query optimizer can generate a more efficient query plan. Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.
These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required. The following example uses the MERGE statement to bulk load data from StockData.txt, a flat file, into the target table dbo.Stock. By defining a primary key constraint onStockName in the target table, a clustered index is created on the column used to join with the source data. The ORDER and UNIQUE hints are applied to the Stock column in the data source, which maps to the clustered index key column in the target table.
Before running this example, create a text file named 'StockData.txt' in the folder C:\SQLFiles\. The file should have two columns of data separated by a comma
USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
BULK 'C:\SQLFiles\StockData.txt',
FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
ROWS_PER_BATCH = 15000,
ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO
reference:
https://msdn.microsoft.com/en-us/library/cc879317.aspx
https://technet.microsoft.com/en-us/library/bb510625.aspx
http://stackoverflow.com/questions/17967374/sql-merge-statement-not-working-in-stored-procedure
http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm
