Posted by Erland Sommarskog on 01/11/06 00:53

Colin Spalding (pupil@alottolearn.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]