| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Merge Transformation |
Merge Transformation |
chugugrace |
chugu |
03/01/2017 |
sql |
integration-services |
conceptual |
|
|
[!INCLUDEsqlserver-ssis]
The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.
By including the Merge transformation in a data flow, you can perform the following tasks:
-
Merge data from two data sources, such as tables and files.
-
Create complex datasets by nesting Merge transformations.
-
Remerge rows after correcting errors in the data.
The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:
-
The transformation inputs are not sorted.
-
The combined output does not need to be sorted.
-
The transformation has more than two inputs.
The Merge Transformation requires sorted data for its inputs. For more information about this important requirement, see Sort Data for the Merge and Merge Join Transformations.
The Merge transformation also requires that the merged columns in its inputs have matching metadata. For example, you cannot merge a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.
In the [!INCLUDEssIS] Designer, the user interface for the Merge transformation automatically maps columns that have the same metadata. You can then manually map other columns that have compatible data types.
This transformation has two inputs and one output. It does not support an error output.
You can set properties through the [!INCLUDEssIS] Designer or programmatically.
For more information about the properties that you can programmatically, click one of the following topics:
For details about how to set properties, see the following topics:
Use the Merge Transformation Editor to specify columns from two sorted sets of data to be merged.
Important
The Merge Transformation requires sorted data for its inputs. For more information about this important requirement, see Sort Data for the Merge and Merge Join Transformations.
Output Column Name
Specify the name of the output column.
Merge Input 1
Select the column to merge as Merge Input 1.
Merge Input 2
Select the column to merge as Merge Input 2.
Merge Join Transformation
Union All Transformation
Data Flow
Integration Services Transformations