SELECT INTO USING UNION QUERY

derived-table, sql, sql-server, t-sql, union

Solution

You have to define a table alias for a derived table in SQL Server:

SELECT x.* 
  INTO [NEW_TABLE]
  FROM (SELECT * FROM TABLE1
        UNION
        SELECT * FROM TABLE2) x

"x" is the table alias in this example.

Problem

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works ``` SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 ``` Query2: Does not Work. Error: `Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'.` ``` SELECT * INTO [NEW_TABLE] FROM ( SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 ) ``` Thanks!

Original source