Difference and similarity between merge and merge join transformation?
Merge Transofrmations Merge Join Transformation
1)The data from 2 input paths are merged into one 1) The data from 2 inputs are merged based on some common key.
2)Works as UNION ALL 2) JOIN (LEFT, RIGHT OR FULL)
3)Supports 2 Datasets 3)1 Dataset
4)Metadata for all columns needs to be same 4) Key columns metadata needs to be same.
Pre-requisites
5)Data must be sorted.
Merged columns should have same datatype i.e. if merged 5) Data must be sorted.
column is EmployeeName with string of 25 character in Input 1,
it can be of less than or equal to 25 characters for merging to
happen.
Merged columns should have same datatype i.e. if merged column is EmployeeName with string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.
Limitations
Only 2 input paths can be merged.
Does not support error handling. 6) Does not support error handling.
Use
Merging of data from 2 data source
Can create complex datasets using nesting merge transformation,
7) When data from 2 tables having foreign key relationship needs to present based on common key.
Merge Transofrmations Merge Join Transformation
1)The data from 2 input paths are merged into one 1) The data from 2 inputs are merged based on some common key.
2)Works as UNION ALL 2) JOIN (LEFT, RIGHT OR FULL)
3)Supports 2 Datasets 3)1 Dataset
4)Metadata for all columns needs to be same 4) Key columns metadata needs to be same.
Pre-requisites
5)Data must be sorted.
Merged columns should have same datatype i.e. if merged 5) Data must be sorted.
column is EmployeeName with string of 25 character in Input 1,
it can be of less than or equal to 25 characters for merging to
happen.
Merged columns should have same datatype i.e. if merged column is EmployeeName with string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.
Limitations
Only 2 input paths can be merged.
Does not support error handling. 6) Does not support error handling.
Use
Merging of data from 2 data source
Can create complex datasets using nesting merge transformation,
7) When data from 2 tables having foreign key relationship needs to present based on common key.