CODEWITHSUNDEEP
sqldatabase
John Humphreys
John Humphreys

Reputation: 39254

Insert from table a to table b where records in a don't exist in b

I have two tables with the same schemas, table A and table B:

| Table [A]        | Table [B]   
| id    value      | id    value
| 1     15         | 1     19
| 2     18         | 3     28
| 5     22         | 4     39

I would like to do:

insert into a select * from b where [b.id not exists in a]

Question: What's the syntax for this - in particular, the portion in the brackets?

Upvotes: 2

Views: 3366

Answers (3)

steoleary
steoleary

Reputation: 9278

If you are on SQL 2008 + you could also use a merge to achieve this, giving you the advantage of being able to easily add conditions for not only where rows don't exist in the target table, but also what to do if the IDs match but the value is different (update maybe) or if there is a row in the target table that doesn't exist in the source (delete maybe):

MERGE TableB AS TARGET
USING TableA AS SOURCE
ON (target.ID = source.ID) 

WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Value) VALUES (source.ID, source.Value);

More information on using merge can be found here:

Inserting, Updating, and Deleting Data by Using MERGE

Upvotes: 1

John Woo
John Woo

Reputation: 263693

use INSERT INTO..SELECT statement and LEFT JOIN.

INSERT INTO tableB (id, Value)
SELECT  a.id, a.value
FROM    tableA a
        LEFT JOIN tableB b 
            ON a.ID = b.ID
WHERE   b.id IS NULL

Upvotes: 6

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Like so:

INSERT INTO a(id, value)
SELECT * 
FROM b 
WHERE b.id NOT IN (SELECT id from a)
  AND b.id IS NOT NULL ;

Note that: You have to watch out the NULL values, when using the predicate IN. Otherwise use the NOT EXISTS predicate, it is safer, or using a LEFT JOIN like what @kuyaJohn suggested.

Upvotes: 4

Related Questions