The issue is throughout placing data from staging table throughout import routine.

The machine is really a legacy one I inherited, and temporary as i develop some thing appropriate I wish to patch items to steer clear of the bandwith failing.

Regrettably the ability is available via another application to produce an entry into table, known as CommReceipt. The bottom line is known as CR_Key . should this happen when the car routine runs to place, say 1000 rows we have to import from another system (not my system) with CR_Key values already defined, it fails.

Generate an income view it I've a number of options, but all suggestions is going to be appreciated continuing to move forward for top means to fix this problem (both lengthy and temporary fixes) .

It's area of the intend to eliminate functionality within the rogue application (but this can be a legacy system, designed in legacy unfamilar language and can take a little of effort)

How do you cope with the main key breach. Can One continue, confirming the breach to cope with after running data place.

UPDATE: the main key CR_Key also is an identity , it is possible to method to remove rows which shouldn't be there and place rows utilizing the same ID. I presume....I turn the identity off, then specify unique values within the 'missing rows', is the fact that plausible? I don't have to auto increment id now, theinsert routine has ID's

Thanks

You could utilize an rather than place trigger. Within the trigger, do an Place in to the table, where not is available CommReceipt.CR_Key = placed.CR_Key.

Create trigger T_CommReceiptInsteadOfInsert on CommReceipt
Instead of Insert
As
Begin

--Insert duplicate records into another table
Insert Into CommReceipt_Duplicates(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

--Insert non duplicate records
Insert Into CommReceipt(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where not exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

End

maybe you could utilize a trigger, to ensure the place can be achieved (the PK don't exist). When the PK already exist, you are able to store some good info in other table like a log, and cancel the place creating a rollback and starting the best.

First of all, I am presuming the PK clashes are accidental, which the recently-placed rows truly are independent organizations (instead of something which should better happen to be handled by an UPDATE. Next, I am presuming you cannot drop that primary key and take care of the "key" clashes having a job running following the batch place (or make use of an alternate primary key altogether).

You've got a couple of options accessible to you if you are utilizing an identity in your primary key column (by which situation your legacy application should be skipping the identity using IDENTITY INSERT because of its effective rows):

  • You can produce a new table with similar schema as CommReceipt, and put an INSTEAD OF trigger on that table, draining the main key and placing into CommReceipt. Then tweak the legacy application to place into that new table.

  • You can adjust the identity seed around the CommReceipt table with a enormous number from selection of the legacy app's rogue secrets. This ought to be treated like a short-term solution.

If you are not using details, then I am presuming you are having your PK values from another logic the legacy application does not get access to. If that is the situation, then you've not one other option rather than fix the legacy application, or make use of an independent store of information because of its rows.

Breach of the PK constraint is really a severe error in sqlserver, which based on BOL for sqlserver 2000 is level 14, so not considered fatal, this really is a little of the trial/error system: the severity degree of the mistake dictates what sqlserver is going to do using the transaction happening: when the severity level is sufficient it'll terminate the transaction in the server level and you will only restart it right from the start.