Search code examples
sqlsql-serverssmsunionunion-all

Create table from simple UNION statement

What is wrong with this union? first 'select' and ')' are incorrect

create table GL_ALL
(
   select *from GL1
)
UNION
(
   select *from GL2
)
UNION
(
   select *from GL3
)
UNION
(
   select *from GL4
)
UNION
(
   select *from GL5
);
Solution

  • That's not the correct syntax for creating a table on the fly in SQL Server, or UNION for that matter.

    Assuming that the schemas of each of your tables are the same

    SELECT *
INTO GL_ALL FROM GL1 UNION
SELECT * FROM GL2 UNION
SELECT * FROM GL3 UNION
SELECT * FROM GL4 UNION
SELECT * FROM GL5;

    As pointed out in comments, this will work for the initial creation of GL_ALL, but not for subsequent inserts after the table is created.

    If you need to append to the table at a later time then the sytax changes to:

    INSERT INTO GL_ALL
SELECT * FROM GL6;

    It's important to realize that the new table will NOT have a primary key nor any foreign keys, indexes (clustered or non), constraints, defaults, etc. that the source tables may have. If these are needed then you will need to manually create them.

    And do note the difference between UNION and UNION ALL, where UNION will exclude duplicate rows.

    Also note, it's best practice to avoid SELECT * and to specifically call out the columns you want to work with - even if it actually is all columns.