Clever Geek Handbook

Merge two tables in SQL and save to 1 new table

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 them to 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 appears:

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

The same if we use

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

Following this answer

Joining a table to itself in SQL and saving the result

The error message will be

Msg 102, Level 15, State 1, Line 5
Invalid syntax near ';'.

Can any guru kindly suggest some comments? Thank!

+3


source to share


5 answers


This syntax works across 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 data types for expressions in the SELECT statement of the subquery must match the data types in the target table of the INSERT statement.

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

If any one row does not delete the INSERT due to a constraint violation or data type conflict, the entire INSERT fails and no rows are inserted.

Any valid subquery can be used in an INSERT statement.

+6


source


I think you need to UNION ALL

, otherwise you won't be able to grab all the data; depends on what data is in the table (duplicates, etc.).



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


source


General syntax

INSERT INTO table2
SELECT * FROM table1;

you can SELECT INTO Statement in this case

with cte as (select 1 col1 ,2 col2
union all
select 2,3) 
select * into #tabletest from cte

select *From #tabletest
+1


source


In both answers, the problem is that you didn't give the alias name for the table in the result. I think you missed ' INTO

' in the instructions INSERT

.

Request 1:

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

INSERT INTO Amide_actives_decoys
SELECT  *
FROM    (
        SELECT * FROM Amide_decoys 
        UNION
        SELECT * FROM Amide_actives 
        ) LU --LU is added.

For query 1, the below is also true

INSERT INTO Amide_actives_decoys
SELECT * FROM Amide_decoys 
UNION
SELECT * FROM Amide_actives

Request 2:

SELECT  * 
INTO    Amide_actives_decoys
FROM    (
        SELECT * FROM Amide_decoys 
        UNION
        SELECT * FROM Amide_actives 
        ) LU -- LU added
+1


source 


create table Amide_actives_decoys
as
select Structure, Name, Active from 
(
SELECT * FROM Amide_decoys 
UNION
SELECT * FROM Amide_actives 
)
;
0


source






More articles:


All Articles