Using MERGE in Integration Services Packages. In the current release of SQL Server Integration Services, the SQL statement in an Execute SQL task can contain a MERGE statement.
This MERGE statement enables you to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement. To use the MERGE statement in a package, follow these steps: Create a Data Flow task that loads, transforms, and saves the source data to a temporary or staging table. Create an Execute SQL task that contains the MERGE statement. Connect the Data Flow task to the Execute SQL task, and use the data in the staging table as the input for the MERGE statement. The remainder of this topic discusses some additional uses for the MERGE statement. For a sample destination component that supports the use of the MERGE statement, see the CodePlex community sample, MERGE Destination. Typically, you use the MERGE statement when you want to apply changes that include inserts, updates, and deletions from one table to another table.
MERGE in Other Scenarios. Inserting, Updating, and Deleting Data by Using MERGE. It is important to understand how the source and target data are merged into a single input stream and how additional search criteria can be used to correctly filter out unneeded rows.
Otherwise,you might specify the additional search criteria in a way that produces incorrect results. Rows in the source are matched with rows in the target based on the join predicate specified in the ON clause. The result is a combined input stream. One insert, update, or delete operation is performed per input row. Depending on the WHEN clauses specified in the statement, the input row might be any one of the following: A matched pair consisting of one row from the target and one from the source. The combination of WHEN clauses specified in the MERGE statement determines the join type that is implemented by the query processor and affects the resulting input stream.
The following table lists the possible join types and indicates when each type is implemented by the query optimizer. . (3 row(s) affected) MERGE (Transact-SQL) WITH <common_table_expression> Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement.
The result set is derived from a simple query and is referenced by the MERGE statement. For more information, see WITH common_table_expression (Transact-SQL). TOP ( expression ) [ PERCENT ] Specifies the number or percentage of rows that are affected. expression can be either a number or a percentage of the rows. The TOP clause is applied after the entire source table and the entire target table are joined and the joined rows that do not qualify for an insert, update, or delete action are removed. Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. Database_name Is the name of the database in which target_table is located. schema_name target_table [ AS ] table_alias. Optimizing MERGE Statement Performance.
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. To improve the performance of the MERGE statement, we recommend the following index guidelines: Create an index on the join columns in the source table that is unique and covering.Create a unique clustered index on the join columns in the target table.
These indexes ensure that the join keys are unique and the data in the tables is sorted. To filter out rows from the source or target tables, use one of the following methods.