Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Union All

Avatar

Level 2

Hi,

 

I want to simply combine ALL results from two data streams - Union joins data based on reconciliation set chosen.

 

Speaking in SQL - I want to simply UNION ALL data.

 

regards

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Klaster 

If you speak about a workflow, you have the UNION activity :

CedricRey_0-1653902205814.png

But the datas source must be the same type. For example you can't union deliveries and recipients (you'll get an error).

If the datas are the same type (if it is not but linked, you can try to use the "Change Axis" activity to get same type) you'll get all the line in the output transition.

 

Cedric

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hi @Klaster 

If you speak about a workflow, you have the UNION activity :

CedricRey_0-1653902205814.png

But the datas source must be the same type. For example you can't union deliveries and recipients (you'll get an error).

If the datas are the same type (if it is not but linked, you can try to use the "Change Axis" activity to get same type) you'll get all the line in the output transition.

 

Cedric

Avatar

Level 2

Hi @CedricRey ,

 

but union internally uses joins which are terrible with performance on really large datasets.

I need something which can be compared to "UNION ALL" in SQL - no joining data, no grouping.

 

 

Avatar

Community Advisor

Hi @Klaster 

I just tested with the SQL Logs activated, the query is a union between 2 SELECT, and not a "JOIN" (it's not an intersection, wich will search the same key into the 2 tables, but a real union, wich will get ALL the lines from all the input tables).

 

INSERT INTO wkfXXXXX_W (iId) SELECT iId FROM wkfXXXXX_Y_Z union SELECT iId FROM wkfXXXXX_X_U

You should test with samples if you're not sure about performance (use the extract activity to get samples of your populations). This is usually a good way to get a performance overview.