CODEWITHSUNDEEP
sql
user3662465
user3662465

Reputation: 13

Merge from 1 table to another

I have the following two tables: 

table 1                   table 2
+-------------+------+    +-------------+------+
|     ssn     |  id  |    |     ssn     |  id  | 
+-------------+------+    +-------------+------+
|  123456789  |  123 |    |  123456789  |  k12 |
|  123456789  |  456 |    |  999999999  |  k11 |
|  123456789  |  789 |    +-------------+------+
|  123456789  |  k12 |
|  999999999  |  799 |
+-------------+------+

What I want to do is to merge the data in table 2 with the data in table 1 if there is no matching id. So 123456789 should be ignored as the member already shows with the id k12. Record 999999999 k11 should be added to table 1.

Upvotes: 0

Views: 58

Answers (3)

Ryan Johnson
Ryan Johnson

Reputation: 88

If this is something you need to do often on large tables, this LEFT JOIN approach may perform a bit faster.

INSERT INTO Table1 
SELECT T2.ssn, T2.id
FROM Table2 T2
LEFT JOIN Table1 t1 ON t1.id = t2.id
WHERE t1.id IS NULL

Upvotes: 0

Kevin Cook
Kevin Cook

Reputation: 1932

INSERT INTO @Table1
( ssn, id)
SELECT t2.ssn, t2.id
FROM @table2 as t2
LEFT JOIN @table1 as t1
    ON t1.id = t2.id or t1.ssn = t2.ssn
WHERE t1.id IS NULL;

if you are worried about duplicates in either id OR ssn, this will only insert when both are unique

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

A few ways to do this. Here's one using NOT EXISTS:

INSERT INTO Table1 
SELECT T2.ssn, T2.id
FROM Table2 T2
WHERE NOT EXISTS (
    SELECT 1 
    FROM Table1 T1
    WHERE T1.id = T2.id)

Or you could use NOT IN:

INSERT INTO Table1
SELECT ssn, id
FROM Table2
WHERE id NOT IN (SELECT id FROM Table1)

Upvotes: 2

Related Questions