problem with duplicate keys

[Pawel Banys]

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Thank you for any suggestions.

Pawel Banys

[Trevor Best]

On Thu, 5 Feb 2004 09:13:28 +0100 in comp.databases.ms-sqlserver,
"Pawel Banys" <vo****@dmz.com.pl> wrote:

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Maybe you should change your script to check for the keys first,
instead of blindly whacking stuff into them e.g.

insert into table1 (pk, column)
select pk, column from table2
where table1.pk not in (select pk from table2)

--
A)bort, R)etry, I)nfluence with large hammer.

[Simon Hayes]

"Pawel Banys" <vo****@dmz.com.pl> wrote in message news:<bv**********@atlantis.news.tpi.pl>...

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Thank you for any suggestions.

Pawel Banys

Are you talking about error 2627 (violation of primary key
constraint)? If so, then the current batch should continue anyway,
unless of course the code has error handling to stop immediately on an
error.

Assuming you have control of the code, you should really avoid the
error instead:

insert into dbo.Destination (col1, col2, ...)
select col1, col2, ...
from dbo.Source s
where not exists (select * from dbo.Destination d
where d.PrimaryKey = s.PrimaryKey)

If you don't control the code, then I don't think there's much you can
do - the error can't be suppressed, and needs to be handled on the
client. Perhaps you can clean up the data somehow before passing it to
the program?

Simon

[Erland Sommarskog]

Pawel Banys (vo****@dmz.com.pl) writes:

There is a program which performs some scripted actions via ODBC on
tables in some database on mssql 2000. Sometimes that program tries to
insert record with key that is already present in the database. The
error comes up and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any
error when the script tries to insert duplicate-key records?

For a unique index - but not a PK or UNIQUE constraint - you can include
the option IGNORE_DUP_KEY. With this setting, SQL Server does not find
it an error condition if you try to insert a duplicate, but leaves @@error
unchanged. Unfortunately, though, the message that is raised, incorrectly
has severity level 16 and not 10, so a client may still belive that
things went wrong.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jordan Bowness]

I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.

insert into server2.database.dbo.table select * from
server1.database.dbo.table

Adding a where clause would take much MUCH longer to perform vs. a
straight insert.

I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?

================
Jordan Bowness
================

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Jordan Bowness (jb_at_bizeworld_dot_ca) writes:

I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.

insert into server2.database.dbo.table select * from
server1.database.dbo.table

Adding a where clause would take much MUCH longer to perform vs. a
straight insert.

I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?

There is the option that I discussed in the article you replied to. That
is, replace the primary key with a unique index with IGNORE_DUP_KEY.

Another possibility is to insert the data into a staging table on the
target server, and then call a stored procedure on the target server
which performs an INSERT targettbl SELECT * FROM staging WHERE NOT EXISTS.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[skirack]

I'm having a similar issue here, i'm using a Insert Trigger to add a row to an EXCEPTIONS table when a condition is met during the insert process. The condition could be met multiple times, but I only need 1 record to go into the EXCEPTIONS table per PK combination. I could limit this using a WHERE clause however the EXCEPTIONS table has a composite primary key and I'm not sure how to check for that.


INSERT INTO STAFFING_EXCEPTIONS ...
SELECT ...
FROM INSERTED I

INNER JOIN STAFFING_3W_REQ REQ ON (I.PROJECT_CODE = REQ.PROJECT_CODE AND I.LOB_CODE = REQ.LOB_CODE AND I.ROW_DATETIME = REQ.ROW_DATETIME AND I.FTE_GOAL != REQ.FTE_GOAL)