SSIS: Merge vs Union All
It seems that the Merge transformation is very similar to the Union All transformation, with the following differences: Merge can only merge 2 sources of data, whereas Union All supports more (I'm not sure how many - my instructor says 256 but there are no sources to confirm that - and MSDN doesn't mention an upper limit). Merge requires inputs to be sorted whereas Union All does not. Apart from these differences, there doesn't seem to be much else that is different. They are both partially blocking, and from my limited tests, I couldn't see any noticeable difference in performance either. Everywhere I've searched, I've found that the recommendation is to use "Merge" whenever possible, and to use "Union All" if there are more than 2 data sources, or if the source data is unsorted. My question is, why? Why would I not just use Union All everywhere? What is the point of Merge? Once again, I'm sorry if this is a very basic/newbie ...