SQL: insert only new records

Question

This must be very trivial but I can't seem to find the solution.

I work with two tables, both without any primary key.

I want to add all the records of the first table to the second table only if they don't exist.

Basically:

INSERT INTO Table2
SELECT Table1.*
FROM Table
WHERE "the record to be added doesn't already exists in Table2"

Answer 1

You could do something like this. You would need to check each relevant column - I have just put in 2 as an example. With a Not Exists clause you can check if a record already existed across multiple columns. With a NOT IN you would only be able to check if a record already existed against one column.

INSERT INTO Table2
SELECT t1.*
FROM Table1 t1
WHERE NOT EXISTS 
(
      SELECT 1 
      FROM table2 t2 WHERE 
      t2.col1 = t1.col1 AND 
      t2.col2 = t1.col2
)