CODEWITHSUNDEEP
sqlsql-server-2008not-exists
Fearghal
Fearghal

Reputation: 11407

How do i insert a value in 1 table if it doesnt exist in another

I have two near identical tables and want to insert the values that exist in the 1st but not in the 2nd into the 2nd.

I do not care about updating the 1st table with missing values in the 2nd, just vice versa.

I tried this select as a test but it returned nothing even for values i know are missing

select * from table1 where Not Exists(Select * from table2)

Upvotes: 0

Views: 40

Answers (2)

StuartLC
StuartLC

Reputation: 107237

You can do this in the general sense with an INSERT statement like the following:

INSERT INTO TABLE2(Col1, Col2, ...)
  SELECT Col1, Col2, ....
  FROM TABLE1 t1
  WHERE NOT EXISTS
  (SELECT 1 FROM Table2 t2 WHERE t2.Criteria = t1.Criteria);

Similarly, the reverse can be done for TABLE2 => TABLE1.

You'll need to specify which columns make up the common criterion. Be wary that if the 2 tables are inserted into independently of eachother (as the requirement suggests), that autogenerated columns like Guids or IDENTITY will differ in the 2 tables and are not suitable as the join criteria.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Assuming you have a field to join both tables together, something like this would work:

INSERT INTO table2 (id, col1)
SELECT id, col1
FROM table1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 
    WHERE table1.id = table2.id)

You can also accomplish the same thing with NOT IN and OUTER JOIN, but you'd still need the matching criteria from both tables.

Upvotes: 1

Related Questions