Preventing the message 'Duplicate key was ignored'

[Colin Spalding]

My problem is that the 'INSERT INTO' query that sends the records to the
table is dynamically compiled in VBA and and the target table has a two
column primary key. I have made a number of attempts at getting 'WHERE
NOT EXISTS' to cure the problem but so far without success and previous
postings have resulted in advice to create an 'ignore duplicates' index.
This solved the problem in as
much as it allowed the SQL to insert the records that did not already
exist but resulted in the message appearing every time the user ran the
the query. Whilst this is not a major problem it is vaguely irritating
and I would like to find a way to stop it happening. I suspect that the
solution may involve using the @@ERROR command but I am not sure of the
syntax.
Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Colin Spalding (pu***@alottole arn.com) writes:

My problem is that the 'INSERT INTO' query that sends the records to the
table is dynamically compiled in VBA and and the target table has a two
column primary key. I have made a number of attempts at getting 'WHERE
NOT EXISTS' to cure the problem but so far without success and previous
postings have resulted in advice to create an 'ignore duplicates' index.
This solved the problem in as
much as it allowed the SQL to insert the records that did not already
exist but resulted in the message appearing every time the user ran the
the query. Whilst this is not a major problem it is vaguely irritating
and I would like to find a way to stop it happening. I suspect that the
solution may involve using the @@ERROR command but I am not sure of the
syntax.

In SQL 2000, there is no possibility to suppress the error. You will
have to this from your client.

In SQL 2005, you can trap the error with new TRY-CATCH syntax.

But the solution with ignore duplicates is really lousy. Instead do
one of:

IF NOT EXISTS (SELECT *
FROM tbl
WHERE pkcol1 = @val1
AND pkcol2 = @val2)
INSERT tbl (pkcol1, pkcol2, ....
VALUES (@val1, @val2, ....)

or

INSERT tbl (pkcol1, pkcol2, ...
SELECT @val1, @val2, ...
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE pkcol1 = @val1
AND pkcol2 = @val2)

Personally, I prefer the first alternative.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx