SQL Insert rows into another table if they aren't duplicates

Question

Hello I'm having trouble figuring out how to insert rows that are not duplicates from one table to another, I have in mind something like this

INSERT INTO Table1
       SELECT id, name, lastname FROM Table2
              WHERE id.Table1 != id.Table2 
              AND name.Table1 != name.Table2 
              AND lastname.Table1 != lastname.Table2

How to write that condition that specifies not to insert if it already exists?

EDIT: I forgot to mention that I needed to match only certain columns since the tables contain identity 1,1 my solution is as follows:

    MERGE Table1 as t1
    USING (SELECT name, lastname FROM Table2) AS t2 (name, lastname) 
    ON t1.[name] = t2.[name] AND t1.[lastname] = t2.[lastname] 
    WHEN NOT MATCHED 
    THEN
    INSERT (name, lastname) VALUES (t2.[name], t2.[lastname]);

Of course this is just an example I'm using a lot more columns and data

edit: not a duplicate since the solutions presented match the entire table

You may want to check out this thread where it looks like your question has been answered already: https://stackoverflow.com/questions/656012/copy-rows-from-one-table-to-another-ignoring-duplicates — I Am Root, Oct 03 '17 at 20:33
Thanks for the replies, but none of that worked for my case I found the solution with MERGE — Roberto Torres, Oct 04 '17 at 15:51

score 5 · Answer 1 · answered Oct 03 '17 at 20:30

5

One way to approach this is to use the except operator:

INSERT INTO Table1
SELECT id, name, lastname FROM Table2
EXCEPT
SELECT id, name, lastname FROM Table1

answered Oct 03 '17 at 20:30

Mureinik

252,575
45
248
283

Thank you for your reply, I forgot to mention that I needed to match certain columns but not all, I found the solution with merge – Roberto Torres Oct 04 '17 at 15:58

score 1 · Answer 2 · answered Oct 03 '17 at 20:33

You may use NOT EXISTS. Please be aware that table name is always before attribute name.

 INSERT INTO Table1
   SELECT id, name, lastname 
   FROM Table2
   WHERE NOT EXISTS (
             SELECT * 
             FROM Table1 
             WHERE Table1.id = Table2.id AND 
                   Table1.name = Table2.name AND 
                   Table1.lastname = Table2.lastname
           )

SQL Insert rows into another table if they aren't duplicates

2 Answers2