I have the following code y DSA:
Select * from TableA
union
Select * from TableB
union
Select * from Table C
I want to replace this code with a table
Solved
How to create a union Table in DSA
+1Best answer by Christian Hauggaard
Hi
This can be achieved by using a Junk Dimension table. If you add create a new table and add the mappings from each source table (i.e. TableA, TableB and TableC), you will see that you get duplicate records (i.e. UNION ALL). If you then right click on the new table, and under advanced, select “Add Junk Dimension Table” and then select the columns that you would like to include. Please see more info about Junk Dimensions here.
There are other ways to achieve this, such as creating a view with UNION statement (and then using a table insert to insert the data from the view into a table). Alternatively, Primary Keys could be used to ensure that records are not duplicated. Or a custom table insert could also be used.
+5Hi
This can be achieved by using a Junk Dimension table. If you add create a new table and add the mappings from each source table (i.e. TableA, TableB and TableC), you will see that you get duplicate records (i.e. UNION ALL). If you then right click on the new table, and under advanced, select “Add Junk Dimension Table” and then select the columns that you would like to include. Please see more info about Junk Dimensions here.
There are other ways to achieve this, such as creating a view with UNION statement (and then using a table insert to insert the data from the view into a table). Alternatively, Primary Keys could be used to ensure that records are not duplicated. Or a custom table insert could also be used.
+8Another alternative: map Table A, B, C into one table and use Advanced > Aggregate table to do a SELECT DISTINCT / GROUP BY to create unique records.
Personally I would always want to have the duplicates explicitly in staging as this may actually be a process or data-entry issue in the source systems that could be solved to everyone's benefit
+5
+1I used customer Insert.
Thanks for your help
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
