SELECT INTO USING UNION QUERY

Question

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!

Wellestablished

Answer 1

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.

Crashaw

When I try this, it doesn't give me the sum of number of records of TABLE1 and TABLE2, it's always less. Would you happen to know why this happens? Thank you! – Unreconstructed

@Unreconstructed - This is likely due to having the same row in both tables, it will only make one record for the union. If you use UNION ALL it will not remove duplicates. – Cormac

Answer 2

select *
into new_table
from table_A
UNION
Select * 
From table_B

This only works if Table_A and Table_B have the same schemas

Priority

You can use three (and four if the table is on a Linked Server instance) name format to access other schemas and/or databases. – Crashaw

I think this is the neatest, simplest answer. I tried it, assuming that it might only add the rows from table_A to new_table. I was pleasantly surprised that rows from both table_A and table_B ended up in new_table. – Dagmardagna

Answer 3

You can also try:

create table new_table as
select * from table1
union
select * from table2

Feck

Answer 4

INSERT INTO #Temp1
SELECT val1, val2 
FROM TABLE1
 UNION
SELECT val1, val2
FROM TABLE2

Parcel

Useful but it doesn't create the table. – Dufour

Answer 5

Here's one working syntax for SQL Server 2017:

USE [<yourdb-name>]
GO

SELECT * INTO NEWTABLE 
FROM <table1-name>
UNION ALL
SELECT * FROM <table2-name>

Insignificant

Answer 6

In MS Access you can:

SELECT x.* INTO NEWTABLE
FROM (
SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2) AS x

It's the same a OMG Ponies's answer except the keyword "AS" is used.

Dustan

This question is for Microsoft SQL Server not Microsoft Access, so correcting the syntax for a different platform doesn’t help people with this problem. At best, this could be a comment on the referenced answer. – Siqueiros