Save results of the union all statement into either a separate table

Question

I want to save results of the union all statement into either a separate table or the H(alf)1 table. I have tried create table and insert into but there is some syntax error I can't seem to figure out. here is one variation I tried

SELECT *
INTO [H1_2020_trips_cyclistic]
FROM
(
    SELECT * FROM [capstone].[dbo].[Divvy_Trips_2020_Q1$] AS Q1
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202004-divvy-tripdata$'] AS April
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202005-divvy-tripdata$'] AS May
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202006-divvy-tripdata$'] AS June
)

-- version 2 
CREATE TABLE [H1_2020_trips_cyclistic] AS
(
    SELECT * FROM [capstone].[dbo].[Divvy_Trips_2020_Q1$]
    UNION ALL 
    SELECT * FROM [capstone].[dbo].['202004-divvy-tripdata$']
    UNION ALL 
    SELECT * FROM [capstone].[dbo].['202005-divvy-tripdata$']
    UNION ALL 
    SELECT * FROM [capstone].[dbo].['202006-divvy-tripdata$'] 
);

Both are not working for me as the SQL Server is giving the following error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('

Any help would be greatly appreciated (PS: union all command is working the problem is I don't know how to store the result).

Answer 1

Your SQL Server query seems syntactically correct. However, while the subquery doesn't need an alias in other databases, in SQL Server you need to give it a alias name to avoid an error. Here's how you can do that:

SELECT *
INTO [H1_2020_trips_cyclistic]
FROM
(
    SELECT * FROM [capstone].[dbo].[Divvy_Trips_2020_Q1$] AS Q1
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202004-divvy-tripdata$'] AS April
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202005-divvy-tripdata$'] AS May
    UNION ALL
    SELECT * FROM [capstone].[dbo].['202006-divvy-tripdata$'] AS June
) AS QueryAlias;

For version 2 Query --> In SQL Server, you don't use the CREATE TABLE AS syntax.