Using MERGE Transform in SAP Data Services
This tutorial describes how to use MERGE transform in SAP Data Services (formerly known as BusinessObjects Data Services). A short video is also provided to provide hands-on.
This article is part of our comprehensive data services tutorial Learning SAP Data Services - Online Tutorial, you may want to check that tutorial first if you have not already done so.
Why Merge Transform?
Merge transform is basically used to combine multiple incoming data sets to produce a single output dataset with the same schema (structure) as that of the input data set.
This is same as performing SQL UNION ALL operation.
Please note that structure of the input data sets must be same in order for you to combine them inside merge transform.
Example of Application of Merge Transform (Video)
The video below illustrates the hands-on application of merge transform. In this example, we will take 3 different flat files with necessarily the same structure as our source and then use merge transform to merge the data in these files together and load them in a single target table.
How to do UNION using Merge Transform
Although the merge transform actually does UNION ALL operation, we can use a query transform after the merge transform to achieve pure SQL UNION. The video above describes how to do this. Basically all you have to do is to use a Query Transform after Merge Transform and then check "Distinct rows" option under the "SELECT" tab of the query transform.