Primary Key constraint
-
I am receiving a file that may on occasion contain a value for the Primary Key that already exists in the table I want to load the file into.
Does anyone know how to get the file to load w/out disabling the Primary Key constraint? I do not want to load the duplicate record.
Thanks,
Jeff
"Keep Your Stick On the Ice" ..Red Green -
Bring your file into a staging table. Then in your insert query [into your final destination table], do something like:
INSERT INTO maintable(pk_field, field1, field2, etc...)
SELECT s.pk_field, s.field1, s.field2, etc...
FROM stagingtable s
WHERE NOT EXISTS(SELECT NULL FROM maintable m
WHERE s.pk_field = m.pk_field)
hth,
Michael
Michael Weiss
Michael Weiss -
Thanks Michael. I thought about that approach and am still hoping for something different.
...Jeff
"Keep Your Stick On the Ice" ..Red Green -
Another approach:
INSERT INTO maintable(pk_field, field1, field2, etc...)
SELECT s.pk_field, s.field1, s.field2, etc...
FROM stagingtable s
WHERE s.pk_field not in(SELECT pk_field FROM maintable m)
-
Michael's tactic of only loading PKs that aren't already present in the table is pretty much the only way to go. The only shortcut I can think of to his strategy is to put that logic (...where PK not in <current set>) in the initial load, in whatever tool/utility you are using to read the file. Is that possible or practical?
Philip
-
If there's other processing you want to do with the duplicate records, like writing them to a text file, you could use a multiphase DTS package. However if you just want to ignore the duplicates, the staging table would be more efficient.
-
Thanks for the input everyone. I was hoping DTS could push it to an Exception file. The reason I was and am trying to avoid a temp staging table is because it already is a temp staging table
I am going to take a shot at xuthus's suggestion.
BTW, would BCP offer a workaround to the PK's?
"Keep Your Stick On the Ice" ..Red Green -
The Transform Data Task Properties - Option tab allows you to specify "Max error count" which is supposed to allow the package to keep going until the maximum is reached. If you know about how many duplicates you have, you can experiment with this number. Given the unpredictability of data, I've not had good luck doing this, but sometimes it comes in handy, and you were looking for some alternatives. Also on this tab is where you can specify the exceptions file for the bad data.
-
Jeff:
1) If you already have a staging table, you do not have to create an additional one 😉
2) If you are directly BCPing in your data into your table without any staging table you could try the switch:
BOL:
quote:
-m max_errorsSpecifies the maximum number of errors that can occur before the bulk copy operation is canceled. Each row that cannot be copied by bcp is ignored and counted as one error. If this option is not included, the default is 10.
And of course for your errors to get reported:
quote:
-e err_fileSpecifies the full path of an error file used to store any rows bcp is unable to transfer from the file to the database. Error messages from bcp go to the user's workstation. If this option is not used, an error file is not created.
3) You could replace your PK with a UNIQUE INDEX and enable the option to ignore duplicates.
-
If the record with the duplicate primary key is an exact duplicate you could add an INSTEAD of INSERT trigger.
This solution checks the main table for matching primary keys in the inserted records. It deletes the matching records to allow the inserted records to be added.
CREATE TRIGGER ti_dupID ON maintable
INSTEAD OF INSERT
AS
DELETE
FROM maintable
WHERE pk_field IN
(SELECT pk_field
FROM INSERTED )
INSERT INTO
maintable(pk_field, field1, field2, etc...)
SELECT s.pk_field, s.field1, s.field2, etc...
FROM inserted
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply