Duplicate Key Ignored

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

Since you want to avoid inserting rows from the source already existing in the destination, have you tried inserting based on a select that excludes those existing rows?

One way to construct it, is by a LEFT JOIN query.

INSERT destination
( col1, col2 ..... )
SELECT col1, col2 ....
FROM source s
LEFT JOIN
destination d
ON s.pk = d.pk
WHERE d.pk IS NULL

/Kenneth

|||A better way in that case is to use a subselect:

INSERT INTO destination
(col1, col2, ...)
SELECT col1, col2, ...
FROM source
WHERE pk NOT IN (
SELECT pk
FROM destination
)|||

Rick Brown wrote:

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

I have the same problem, when my app runs the stored procedure, the error appears and the data is not retrieved. I don't think I can use the subquery solution, so I would like to get it working with Ignore_Dup_Key.

Isn't the purpose of Ignore_Dup_Key to allow the data to be returned without inserting the duplicated data?

Thanks for your time