CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel
Daniel

Reputation: 1491

Remove invalid FK data after bulk load

I bulk load data into MS SQL. In order to do so effectively I temporarily disable FK constraints on a table (one table 8 constraints all of which have cascade delete "Set NULL"). Before I re-enable constraint checking I need to check for invalid foreign keys and set them to NULL.

How would I go about achieving this goal? Thanks.

Upvotes: 1

Views: 46

Answers (1)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

UPDATE MyMainTable
SET TableAID = CASE WHEN TableA.ID IS NULL THEN NULL ELSE TableAID END,
    TableBID = CASE WHEN TableB.ID IS NULL THEN NULL ELSE TableBID END,
FROM MyMainTable
     LEFT JOIN TableA ON MyMainTable.TableAID = TableA.ID
     LEFT JOIN TableB ON MyMainTable.TableBID = TableB.ID

Upvotes: 3

Related Questions