Union All Transformation

Union All Transformation

- It allows us to union (merge horizontally) from more than 1 input
- Unlike Merge Transformation (more details), it allows more than 2 inputs also and inputs are not required to be in a sorted order


EXAMPLE

Pre-requisite

Following script has to be executed

CREATE TABLE UnionAllDemo1 (MID INT, MName VARCHAR(10))

CREATE TABLE UnionAllDemo2 (MID INT, MName VARCHAR(10))

CREATE TABLE UnionAllDemo3 (MID INT, MName VARCHAR(10))

CREATE TABLE UnionAllDemoOutput (MID INT, MName VARCHAR(10))

INSERT INTO UnionAllDemo1 (MID,MName) VALUES (1,'Nisarg'),(5,'Megha'),(2,'Swara')

INSERT INTO UnionAllDemo2 (MID,MName) VALUES (4,'Mulik'),(3,'Dipa'),(8,'Nidhi')

INSERT INTO UnionAllDemo3 (MID,MName) VALUES (7,'Kamlesh'),(6,'Pratibha'),(9,'Mahesh')

Steps

1. Add Data Flow Task in Control Flow
2. Drag 3 source in DFT and they should point to UnionAllDemo1, UnionAllDemo2, UnionAllDemo3 respectively.
3. Drag UnionAll transformation and connect then with all 3 sources one by one. This will not prompt for any input window.
4. Connect UnionAll trans. with Destination which should point to UnionAllDemoOutput table.
5. Whole structure will look like this.