CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
user1570048
user1570048

Reputation: 880

sql insert if exists delete if not exists between 2 tables

Looking for an SQL Query to do the following:

  1. insert all values of column x of table1 to column y of table2 if they don't exist in table2
  2. delete records from column table2 if the value of clumn y doesn't exist anymore in column x of table1

I tried insert or ignore for the insert query with no luck in sql server 2008 R2

Upvotes: 1

Views: 3697

Answers (3)

JodyT
JodyT

Reputation: 4412

You can use the Merge Statement

MERGE table2 AS t2
USING table1 AS t1
ON (t2.y = t1.x) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(y) VALUES(t1.x)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

Sounds like a perfect fit for an SQL Server MERGE;

MERGE table2 AS t2
USING table1 AS t1
ON (t2.y = t1.x)
WHEN NOT MATCHED BY TARGET 
  THEN INSERT (y) VALUES(t1.x)
WHEN NOT MATCHED BY SOURCE
  THEN DELETE;

An SQLfiddle to test with.

Upvotes: 3

sgeddes
sgeddes

Reputation: 62831

This will insert you all records in Table1, colX, into Table2, colY:

INSERT INTO Table2 (ColY)
SELECT ColX 
FROM Table1
WHERE ColX NOT IN (SELECT ColY FROM Table2)

You can't delete all records in a column. You can however set those to NULL, or if you want to delete the rows, you can do that as well. Just let us know.

DELETE FROM Table2
WHERE ColY NOT IN (SELECT ColX From Table1)

OR 

UPDATE Table2
SET ColY = NULL
WHERE ColY NOT IN (SELECT ColX From Table1)

Good luck.

Upvotes: 2

Related Questions