UNION ALL Data Migration

Data migration considerations for UNION ALL.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

UNION ALL is a SQL operator that allows the combination of multiple resultsets. The syntax is the following:

query_expression_1 UNION [ ALL ] query_expression_2

For more information, please review the following Teradata documentation.

Column Size differences

Even though the operator is translated into the same operator in Snowflake, there could be detailed differences in functional equivalence. For example, the union of different columns which have different column sizes. Teradata does truncate the values when the first SELECT statement contains less space in the columns.

Teradata behavior

Same behavior in ANSI and TERA session modes.

For this example, the following input will show the Teradata behavior.

CREATE TABLE table1
(
col1 VARCHAR(20)
);

INSERT INTO table1 VALUES('value 1 abcdefghijk');
INSERT INTO table1 VALUES('value 2 abcdefghijk');

CREATE TABLE table2
(
col1 VARCHAR(10)
);

INSERT INTO table2 VALUES('t2 row 1 a');
INSERT INTO table2 VALUES('t2 row 2 a');
INSERT INTO table2 VALUES('t2 row 3 a');

CREATE OR REPLACE TABLE table1
(
col1 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table1
VALUES ('value 1 abcdefghijk');

INSERT INTO table1
VALUES ('value 2 abcdefghijk');

CREATE OR REPLACE TABLE table2
(
col1 VARCHAR(10)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table2
VALUES ('t2 row 1 a');

INSERT INTO table2
VALUES ('t2 row 2 a');

INSERT INTO table2
VALUES ('t2 row 3 a');

Case 1 - one single column: UNION ALL for a column varchar (20) over a column varchar (10)

For this case, the functional equivalence is the same

SELECT col1 FROM table1
UNION ALL
SELECT col1 FROM table2;

SELECT
col1 FROM
table1
UNION ALL
SELECT
col1 FROM
table2;

Case 2 - one single column: UNION ALL for a column varchar (10) over a column varchar (20)

In this case, the function equivalence is not the same.

The following case does not show functional equivalence in Snowflake. The column values should be truncated as in the Teradata sample.

SELECT col1 FROM table2
UNION ALL
SELECT col1 FROM table1;

SELECT
col1 FROM
table2
UNION ALL
SELECT
col1 FROM
table1;

Workaround to get the same functionality

In this case, the size of the column of the table2 is 10 and the table1 is 20. So, the size of the first column in the query should be the element to complete the LEFT() function used here. Review more information about the Snowflake LEFT function HERE.

SELECT col1 FROM table2 -- size (10)
UNION ALL
SELECT LEFT(col1, 10) AS col1 FROM table1;

Case 3 - multiple columns - same size by table: UNION ALL for columns varchar (20) over columns varchar (10)

For this case, it is required to set up new data as follows:

CREATE TABLE table3
(
col1 VARCHAR(20),
col2 VARCHAR(20)
);

INSERT INTO table3 VALUES('value 1 abcdefghijk', 'value 1 abcdefghijk');
INSERT INTO table3 VALUES('value 2 abcdefghijk', 'value 2 abcdefghijk');

CREATE TABLE table4
(
col1 VARCHAR(10),
col2 VARCHAR(10)
);

INSERT INTO table4 VALUES('t2 row 1 a', 't2 row 1 b');
INSERT INTO table4 VALUES('t2 row 2 a', 't2 row 2 b');
INSERT INTO table4 VALUES('t2 row 3 a', 't2 row 3 b');

CREATE OR REPLACE TABLE table3
(
col1 VARCHAR(20),
col2 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table3
VALUES ('value 1 abcdefghijk', 'value 1 abcdefghijk');

INSERT INTO table3
VALUES ('value 2 abcdefghijk', 'value 2 abcdefghijk');

CREATE OR REPLACE TABLE table4
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table4
VALUES ('t2 row 1 a', 't2 row 1 b');

INSERT INTO table4
VALUES ('t2 row 2 a', 't2 row 2 b');

INSERT INTO table4
VALUES ('t2 row 3 a', 't2 row 3 b');

Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

select col1, col2 from table3
union all
select col1, col2 from table4;

SELECT
col1, col2 FROM
table3
UNION ALL
SELECT
col1, col2 FROM
table4;

Case 4 - multiple columns - same size by table: UNION ALL for columns varchar (10) over columns varchar (20)

In this case, the function equivalence is not the same.

select col1, col2 from table4
union all
select col1, col2 from table3;

SELECT
col1, col2 FROM
table4
UNION ALL
SELECT
col1, col2 FROM
table3;

Workaround to get the same functionality

Apply the column size to the second SELECT on the columns to get the same functionality.

SELECT col1, col2 FROM table4 -- size (10)
UNION ALL
SELECT LEFT(col1, 10) AS col1, LEFT(col2, 10) AS col2 FROM table3;

Case 5 - multiple columns - different sizes by table: UNION ALL for columns varchar (10) over columns varchar (20)

For this case, it is required to set up new data as follows:

CREATE TABLE table5
(
col1 VARCHAR(20),
col2 VARCHAR(12)
);

INSERT INTO table5 VALUES('value 1 abcdefghijk', 'value 1 abcdefghijk');
INSERT INTO table5 VALUES('value 2 abcdefghijk', 'value 2 abcdefghijk');

CREATE TABLE table6
(
col1 VARCHAR(10),
col2 VARCHAR(5)
);

INSERT INTO table6 VALUES('t2 row 1 a', 't2 row 1 b');
INSERT INTO table6 VALUES('t2 row 2 a', 't2 row 2 b');
INSERT INTO table6 VALUES('t2 row 3 a', 't2 row 3 b');

CREATE OR REPLACE TABLE table5
(
col1 VARCHAR(20),
col2 VARCHAR(12)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table5
VALUES ('value 1 abcdefghijk', 'value 1 abcd');

INSERT INTO table5
VALUES ('value 2 abcdefghijk', 'value 2 abcd');

CREATE OR REPLACE TABLE table6
(
col1 VARCHAR(10),
col2 VARCHAR(5)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "10/14/2024" }}'
;

INSERT INTO table6
VALUES ('t2 row 1 a', 't2 1b');

INSERT INTO table6
VALUES ('t2 row 2 a', 't2 2b');

INSERT INTO table6
VALUES ('t2 row 3 a', 't2 3b');

Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

select col1, col2 from table5
union all
select col1, col2 from table6;

SELECT
col1, col2 FROM
table5
UNION ALL
SELECT
col1, col2 FROM
table6;

Case 6 - multiple columns - different sizes by table: UNION ALL for columns varchar (20), varchar(10) over columns varchar (10), varchar(5)

In this case, the function equivalence is not the same.

select col1, col2 from table6
union all
select col1, col2 from table5;

SELECT
col1, col2 FROM
table6
UNION ALL
SELECT
col1, col2 FROM
table5;

Workaround to get the same functionality

The column with the smallest size from the first SELECT is used to determine the size of the columns from the second SELECT.

SELECT
col1, col2 FROM
table6
UNION ALL
SELECT
LEFT(col1, 5) as col1, LEFT(col2, 5) AS col2 FROM
table5;

Case 7 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

Use the data set up here. Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

select col1 || col2 from table3
union all
select col1 || col2 from table4;

SELECT
col1 || col2 FROM
table3
UNION ALL
SELECT
col1 || col2 FROM
table4;

Case 8 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

This case has functional differences.

select col1 || col2 from table4
union all
select col1 || col2 from table3;

SELECT
col1 || col2 FROM
table4
UNION ALL
SELECT
col1 || col2 FROM
table3;

Workaround to get the same functionality

The sum of the column sizes of the less big column should be used in the LEFT function. For example, the less big column is varchar(10), so the limit of the LEFT function should be 20 (10 + 10).

The sum of the first SELECT if this is less big, it would be used for the truncation of the values.

SELECT
col1 || col2 FROM
table4
UNION ALL
SELECT
LEFT(col1 || col2, 20) FROM
table3;

Other considerations about column size differences

CHAR and VARCHAR behave the same.
Number columns may behave differently. The numbers cannot be truncated, so there is an overflow in the Teradata environment. So, this is not applied to these data types. Review the following example:

-- Teradata number sample 
CREATE TABLE table11
(
col1 NUMBER(2)
);

INSERT INTO table11 VALUES(10);
INSERT INTO table11 VALUES(10);

CREATE TABLE table12
(
col1 NUMBER(1)
);

INSERT INTO table12 VALUES(1);
INSERT INTO table12 VALUES(1);
INSERT INTO table12 VALUES(1);

-- ERROR!  Overflow occurred when computing an expression involving table11.col1
SELECT col1 FROM table12
UNION ALL
SELECT col1 FROM table11;

PreviousData Migration Considerations NextSession Modes in Teradata

Last updated 5 months ago

hashtagColumn Size differences

hashtagTeradata behavior

hashtagCase 1 - one single column: UNION ALL for a column varchar (20) over a column varchar (10)

hashtagCase 2 - one single column: UNION ALL for a column varchar (10) over a column varchar (20)

hashtagCase 3 - multiple columns - same size by table: UNION ALL for columns varchar (20) over columns varchar (10)

hashtagCase 4 - multiple columns - same size by table: UNION ALL for columns varchar (10) over columns varchar (20)

hashtagCase 5 - multiple columns - different sizes by table: UNION ALL for columns varchar (10) over columns varchar (20)

hashtagCase 6 - multiple columns - different sizes by table: UNION ALL for columns varchar (20), varchar(10) over columns varchar (10), varchar(5)

hashtagCase 7 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

hashtagCase 8 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

hashtagOther considerations about column size differences

Column Size differences

Teradata behavior

Case 1 - one single column: UNION ALL for a column varchar (20) over a column varchar (10)

Case 2 - one single column: UNION ALL for a column varchar (10) over a column varchar (20)

Case 3 - multiple columns - same size by table: UNION ALL for columns varchar (20) over columns varchar (10)

Case 4 - multiple columns - same size by table: UNION ALL for columns varchar (10) over columns varchar (20)

Case 5 - multiple columns - different sizes by table: UNION ALL for columns varchar (10) over columns varchar (20)

Case 6 - multiple columns - different sizes by table: UNION ALL for columns varchar (20), varchar(10) over columns varchar (10), varchar(5)

Case 7 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

Case 8 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

Other considerations about column size differences