What I'm trying to do is create a new table based off the union of all the other tables(16 in total) since they have the same columns. I'm using Microsoft SQL Server.

select * into NewTable from(
  select *
  from table 1 
  union all

  select *
  from table 2 
  union all
) as sub

...

I keep getting incorrect syntax near '(' expecting ')' or SELECT. Am I suppose to give an alias to each table within the sub query? Not sure what to do.

CodePudding user response：

I think you have an extra Union All statement at the end of your query.

Please try:

select * into NewTable from(
select *
from table 1 
union all

(select *
from table 2 )

) as sub

Depending on the size of your tables, you might want to limit the columns returned instead of selecting all of them.

CodePudding user response：

I went to figure this out for MS SQL, which I don't usually use, just for fun.

Here is a complete example from SQL Fiddle:

CREATE TABLE table_1 (col_1 int);
CREATE TABLE table_2 (col_1 int);

INSERT INTO table_1 VALUES (1), (2);
INSERT INTO table_2 VALUES (100), (200);

SELECT * INTO union_table 
FROM (SELECT * FROM table_1) AS X UNION ALL (SELECT * FROM table_2);

SELECT * FROM union_table;

Returns: