I thought I had a solution to this yesterday but it turns out that indexing with IGNORE_DUP_KEY = ON produces a warning message, which is not acceptable in my application... so I need another solution as I already closed the prior question.
I have a MasterDatabase with more than a million records... the Primary Key is the vendernumber + partnumber.
Each day we get new lists of inventory.. and we need to add records to the MasterDatabase for each new part received.
For example, if today we get a list of 1000 new parts (ventdernumber+partnumber) and only 5 of them are not already in our MasterDatabase, then I want to add those five records. The record that I add would include fields for the vendernumber and Partnumber as well as a bunch of other fields that would be blank.
Using MS Access this process was simple process. I would run an append query using Docmd.setwarnings false ... in this case, I would run a query that tries to append all 1000 records.. but only the five unique records are added.. which is what I want... others are disgarded because a record with the same primary key already exists... A perfect solution for this application. The query would take just a second or less.
Now that I have upsized my database to SQL the routine no longer works because SQL does not have a setwarnings false command.. I tried creating the index with IGNORE_DUP_KEY = ON. The change works as expected except I get a message "Duplicate key was ignored” which requires acknowledgment before the code continues.
This behavior is unacceptable as the command is used as part of a maintenance operation that requires an unattended operation.
I need a way to suppress this message.. or a way to trap the message and programmatically respond to it... or a completely different process..