Thursday, January 29, 2009

Difference between Merge and Union all transformation

I have been asked by many new SSIS developer about difference between Merge and Union all transformation in SSIS.

Well both of them essentially takes outputs from more than one sources and combines them into a single result set but there are couple of differences between two:

a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs

b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.

23 comments:

Shanu said...

many thanks ,i was looking for the same desperately and finally find here


can you please post some more conceptual SSIS/SSRS questions for Interview point of view

Anonymous said...

thanks mate !

Anonymous said...

good answer

Anonymous said...

nice answer, apreciate
Thnx

RingoSchplingo said...

So having read that, why would there ever be a
reason to use the merge operator over the
union all?

Anonymous said...

Then what is the Advantage of using Merge transformation.

Anonymous said...

I treat SSIS UNION ALL transformation same as UNION ALL in TSQL

Kamakshi Suram said...

Can you please explain of there is any difference interms of performace?


---------------

Kamakshi Suram

Rahul Kumar said...

Hi Kamakshi,
Union AlL is faster than Merge even if you have to Sort data after Union All..
Thanks

Anonymous said...

What's the difference Between merge and merge join in ssis

Kamakshi Suram said...

Thanks Rahul

Kamakshi Suram

Azim said...

Thanks a lot For the short and beautiful explanation

srikanth said...

Hi all,

i have a two queries one normal query and second one is stored procedure,if i execute both quires. which one is execute first?
please if any one know give me answer!!!

boyapati ramanjaneyulu said...

Stored procedure will Run fst because Stored procedure is Already precompiled and Stored in database no need to take n/w traffic,but query takes N/w traffic for each statemnt to execute

Boyapati Ramanjaneyulu said...

Diff between Merge and merge join :
Merge join:it Behave likes Normal joins in Sql sever joins(Inner join,left) it adding a new column to data flow Based on Matched values in Common columns

Merge:it just clubing data(union all),no need to join any common columns

Similarity:Both are using merge Transformation before using this transformations

Viggneshwar said...

Why we going to merge when it can be done with union all

juan said...

why might need merge instead of union all because i believe union all is only for records that are already in the data pipieline stream, whereas merge can be used for example, flat files which are not in the pipeline stream yet.?

Anonymous said...

Although the post is about diff btwn Merge and Merge Join, could someone explain when do we use which transformation using a scenario?
Rahul if you haven't yet posted a article on my question, please do that for the benefit of many. Thank you.

Domain KING said...

Thank you for such a wonderful Information !!
Here is a list of Top LINUX INTERVIEW QUESTIONS

Veritas Cluster Interview Questions

SAMBA Server Interview Questions

Linux FTP vsftpd Interview Questions

SSH Interview Questions

Apache Interview Questions

Nagios Interview questions

IPTABLES Interview Questions

Ldap Server Interview Questions

LVM Interview questions

Sendmail Server Interview Questions

YUM Interview Questions

NFS Interview Questions

Read More at :- Linux Troubleshooting

Vijay Sutariya said...

Nice, Keep it up

Anonymous said...

If the source is XML, excel, file, other than a table then we require to use this component in SSIS to extract the data

bipin said...

Great! Simple and sweet explanation!

Anonymous said...

I believe that a great difference between merge and union also is that the merge make that two data source mix the information with some field that share the same information and the sentence Union add all rows of both data sources, making a destination with the total of two data source

Post a Comment