+1Hi everyone, how are you?
I'm trying to combine two tables with the same columns in a Data Model. My goal is to merge the data so I can continue adding and analyzing new information in my views.
I've tried using UNION/UNION ALL, but I haven't been able to get it to work. What is the correct way to combine two tables with the same structure in a Celonis Data Model?
Any help or guidance would be greatly appreciated. Thanks in advance!
Best answer by gagan1
Hi
The UNION ALL is failing because you're running it in the Data Model layer. The model only defines tables and their joins, it can't append rows. You have to combine the data one step earlier, in Data Integration.
In your Data Pool, add a Transformation task and create a combined table there, then add that table to your Data Model and reload. Something like ->
DROP TABLE IF EXISTS "merged_table";
CREATE TABLE "merged_table" AS
SELECT col1, col2 FROM "schema"."table_A"
UNION ALL
SELECT col1, col2 FROM "schema"."table_B";replace schema/table_A/table_B with your actual names
Use UNION ALL (not UNION) unless you actually want to drop duplicates. One thing that quietly breaks it: the columns must line up in count, order and data type across both selects. If a column has the same name but different type in the two tables, cast it (CAST(col AS VARCHAR)) so they match.
If you're on delta/incremental loads use CREATE TABLE like above; if you full-reload every time you can use CREATE VIEW instead, it's a bit lighter. Both can be added to the model.
+6Hi
The UNION ALL is failing because you're running it in the Data Model layer. The model only defines tables and their joins, it can't append rows. You have to combine the data one step earlier, in Data Integration.
In your Data Pool, add a Transformation task and create a combined table there, then add that table to your Data Model and reload. Something like ->
DROP TABLE IF EXISTS "merged_table";
CREATE TABLE "merged_table" AS
SELECT col1, col2 FROM "schema"."table_A"
UNION ALL
SELECT col1, col2 FROM "schema"."table_B";replace schema/table_A/table_B with your actual names
Use UNION ALL (not UNION) unless you actually want to drop duplicates. One thing that quietly breaks it: the columns must line up in count, order and data type across both selects. If a column has the same name but different type in the two tables, cast it (CAST(col AS VARCHAR)) so they match.
If you're on delta/incremental loads use CREATE TABLE like above; if you full-reload every time you can use CREATE VIEW instead, it's a bit lighter. Both can be added to the model.
+8Hi UNION/UNION ALL
+1Hello, everyone!
I followed what @gagan1 recommended, and it worked.
@abhishek.chatu14, the error is:
Error detail:
ERROR: Syntax error at or near "SELECT" at character 166.
Thank you for the information!
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.