Merge 2 tables in SQL and save into 1 new table

Question

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!

Plz refer this : blog.sqlauthority.com/2012/08/29/…

backtrack
– backtrack

2014-11-05 05:49:53 +00:00
Commented Nov 5, 2014 at 5:49 — backtrack
– backtrack, Commented Nov 5, 2014 at 5:49

Multisync · Accepted Answer · 2014-11-05 05:48:00Z

10

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.

answered Nov 5, 2014 at 5:48

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

backtrack Over a year ago

+1 nice answer. Plz add explanation if possible that will be good :)

Simon1979 · Accepted Answer · 2014-11-05 05:50:42Z

4

I think you need to UNION ALL otherwise you may not capture 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;

answered Nov 5, 2014 at 5:50

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

1 Comment

Chubaka Over a year ago

Thanks Simon1979! Here we do need UNION ALL otherwise some data are missing!

King_Fisher · Accepted Answer · 2014-11-05 06:13:30Z

2

The General syntax is

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

answered Nov 5, 2014 at 6:13

King_Fisher

1,2239 gold badges22 silver badges55 bronze badges

Comments

SuperBiasedMan · Accepted Answer · 2016-08-11 10:37:05Z

2

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

edited Aug 11, 2016 at 10:37

SuperBiasedMan

10k11 gold badges52 silver badges76 bronze badges

answered Aug 11, 2016 at 10:18

user6704385

211 bronze badge

1 Comment

SuperBiasedMan Over a year ago

It's best to explain your answers rather than just post code, as the OP may not entirely follow a code only answer.

Jithin Shaji · Accepted Answer · 2014-11-05 06:59:08Z

1

In both your answers, the issue is that you have not given an alias name for the table as a result.I think you missed an 'INTO' in the INSERT statement as well.

Query 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 also is correct

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

Query 2:

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

answered Nov 5, 2014 at 6:59

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

1 Comment

Chubaka Over a year ago

Good and detailed answer too! However, Here we do need UNION ALL otherwise some data are missing!

Vihang Chandra · Accepted Answer · 2022-02-17 22:40:47Z

0

SELECT tab1.firstName, tab1.lastName, tab2.city, tab2.state FROM TABLE_1 tab1 LEFT JOIN TABLE_2 tab2 ON tab1.personId=tab2.personId

answered Feb 17, 2022 at 22:40

Vihang Chandra

1

1 Comment

bazzilic Over a year ago

Hey, welcome to SO! Thank you for your contribution! In your future answers, please consider adding some explanations along with the code snippet. Also, you can use single or triple backticks to escape code snippets and even have code highlighting!

Collectives™ on Stack Overflow

Merge 2 tables in SQL and save into 1 new table

6 Answers 6

1 Comment

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related