If we have two or more tables with the same columns
Table 1
Structure, Name, Active
1,A,1
Table 2
Structure, Name, Active
2,B,0
We would like to combine these two tables and save it into a new one
New Table
Structure, Name, Active
1,A,1
2,B,0
Here is the code
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
)
The following error message will show up
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.
The same thing if we use
SELECT * INTO Amide_actives_decoys
FROM (
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
)
Following this answer
Joining a table onto itself in SQL and saving the result
The error message will be
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.
Could any guru kindly offer some comments? Thanks!
Answer: To do this, the SQL CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2); For example: CREATE TABLE suppliers AS (SELECT * FROM companies WHERE 1=2);
If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table. In this case also, the table will increase to accommodate the new columns.
An SQL query can JOIN multiple tables. For each new table an extra JOIN condition is added. Multi-Table JOINs work with SELECT, UPDATE, and DELETE queries.
create table Amide_actives_decoys
as
select Structure, Name, Active from
(
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
)
;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With