Merge 2 tables in SQL and save into 1 new table

Question

I have two tables with the same columns.

Table 1

Structure, Name, Active
1,A,1

Table 2

Structure, Name, Active
2,B,0

I want to combine these two tables and save them into a new one

New Table

Structure, Name, Active
1,A,1
2,B,0

Here is the code I came up with:

CREATE TABLE Amide_actives_decoys
(
    Structure NVARCHAR(255),
    Name NVARCHAR(255),
    Active INT
)
GO

INSERT Amide_actives_decoys
FROM (
   SELECT * FROM Amide_decoys 
   UNION
   SELECT * FROM Amide_actives 
)

But the following error message shows up:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.

Can someone please help me with this? 

Answer 1

This syntax works in different databases:

INSERT INTO Amide_actives_decoys(Structure, Name, Active)
   SELECT * FROM Amide_decoys 
   UNION
   SELECT * FROM Amide_actives; 

In this form of INSERT, the output of the subquery becomes the set of input values for the INSERT.

Note that the datatypes for the expressions in the SELECT statement subquery must match the datatypes in the target table of the INSERT statement.

All of the rows returned by the subquery are inserted into the Amide_actives_decoys table.

If any one row fails the INSERT due to a constraint violation or datatype conflict, the entire INSERT fails and no rows are inserted.

Any valid subquery may be used within the INSERT statement.

I hope this helps you.