CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20332

How to merge three mostly similar, but somewhat different, tables into one table?

I'm wondering what the the easiest way to merge three tables into one new table. These tables have mostly similar field names, but one table has about 20 more fields than the other two, and the wider table has about 130 fields. Where the field names match, all are exactly the same. The three tables have around 1M, 1.5M, and 2M records.

I could do this.

CREATE TABLE ALLMERGED AS(
    SELECT * FROM TABLE1 
    UNION
    SELECT * FROM TABLE2 
    UNION
    SELECT * FROM TABLE3)

I'm just wondering if the star character is reliable to arrange all fields in the proper order. Is that the best practice?

I am on SQL Server Azure 2019.

Thanks.

Upvotes: 0

Views: 567

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

The first rule of UNION clause is number of columns must be same. As per your description, No of columns are not same so your query will fail.

In order to create a new table you need to explicitly mention the column names like below - 

       SELECT COL1, COL2, COL3 INTO ALLMERGED FROM TABLE1
       UNION ALL
       SELECT COL1, COL2, COL3 FROM TABLE2
       UNION ALL
       SELECT COL1, COL2, COL3 FROM TABLE3;

Also you need to take care that the datatype of all these columns must also be same.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270091

The best practice is to not have tables with the exact same structure. That suggests that you want a single table, with a column to distinguish "1", "2", and "3". In your case, this might be a master table with 110 columns. And then an additional table with 20 columns.

If you do want to use union, then you usually want union all -- unless you specifically want to incur overhead to remove duplicates.

And, you should list the columns explicitly:

select col1, col2, col3 from table1
union all
select col1, col2, col3 from table2
union all
select col1, col2, col3 from table3;

In a union/union all (or other set operation) the columns are accessed by position, not by name. So you want to be absolutely sure the right columns are combined from the different tables.

If you want additional columns, then provide default values in the other tables:

select col1, col2, col3, null as col4 from table1
union all
select col1, col2, col3, null as col4 from table2
union all
select col1, col2, col3, col4 from table3;

Upvotes: 3

Related Questions