CODEWITHSUNDEEP
sql-serverdatabase
KellyM
KellyM

Reputation: 2522

Control Behavior of Constraints During SQL Bulk Insert?

I am curious if there is a way to control how the database responds to foreign and primary key constraints while using the bulk insert command. Specifically, I am inserting data into an employees table (with primary key empID), that is linked to data in other tables (children, spouse, and so on) by that key (empID). Given the logic and purpose behind this application, if, during bulk insert, a duplicate primary key is found, I would like to do the following:

  1. Delete (or update) the 'employee' row that already exists in the database.
  2. Delete all data from other tables associated with that employee (that is children, beneficiaries, and so on).
  3. Insert the new employee data.

If I was inserting data the usual way, that is without bulk insert, I think the task would be quite simple. However, as am using it, I must admit, I am not quite certain how to approach this.

I am certainly not expecting anybody to write my code for me, but I am not quite sure if it is even possible, how to begin, or what the best approach might be. A stored procedure perhaps, or changes to schema?

Thanks so much for any guidance.

Upvotes: 1

Views: 442

Answers (1)

Milney
Milney

Reputation: 6417

The usual solution to this kind of problem is to load the data into a Staging table, perhaps in it's own Schema or even Database. You would then load the actual table from this staging table, allowing you to perform whatever logic is required in an un-restricted manner. This has the added benefit of letting you log/audit/check the logic you are using while loading the 'real' table.

Upvotes: 1

Related Questions