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.


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

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..

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 !!

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

Kevin Lee said...

Hi There,

Hot! That was HOT! Glued to the #topic your proficiency and style!

Unfortunately I still don't anything about Linux, I am just a newbie.
For what I know about Linux it is good for these kind of operation because it is fast. And also, I am interested in learning how to use it because I read that it gives you more opportunity to solve different kind of problems.
The time to recover an ext3 file system after an unclean system shutdown does not depend on the size of the file system or the number of files; rather, it depends on the size of the journal used to maintain consistency. The default journal size takes about a second to recover, depending on the speed of the hardware.
Great effort, I wish I saw it earlier. Would have saved my day :)


Kevin Lee said...

Hello Rahul,

Great post. Well thought out. This piece reminds me when I was starting out Difference between Merge and Union all transformation after graduating from college.

I run AMD Quad Athlon II I have tried installing Mint 18 Cinnamon (It crashed bad) and I have installed Zorin 9 Core, After some struggles (total Noob) I got it installed, however my audio does not work and it looks like no audio drivers/apps have been installed. I had an elementary 3.2 installed by someone else and the audio worked, however it had other issues like would not upload some packages and several of the apps just would not work. In any case it froze once when my wife was on it and voila no PC! So how can get the audio to work on Zorin 9 Core?
I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.


Post a Comment