Posted by Erland Sommarskog on 05/21/07 16:19

nano (nano@nano.ono) writes:
> Does sql server have a way to handle errors in a sproc which would allow
> one to insert rows, ignoring rows which would create a duplicate key
> violation? I know if one loops one can handle the error on a row by row
> basis. But is there a way to skip the loop and do it as a bulk insert?
> It's easy to do in Access, but I'm curious to know if SQL Server proper
> can handle like this. I am guessing that a looping operation would be
> slower to execute?

I'm a little uncertain what you are talking about. In SQL Server "bulk
insert" is a special operation where you load many rows direct from a
file. Or are you still talking about regular SQL statements?

In the latter case, use

INSERT tbl
SELECT ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

which should be the normal way to do it in Access - or any other SQL engine
for that matter - as well.

If you are specifically talking bulk load from file, then above is still
possible in SQL 2005 if you use OPENROWSET(BULK) as the table source. If
you use BULK INSERT or BCP (the only options on SQL 2000), I believe it's
possible by using the IGNORE_DUP_KEY option on the index, but a more
common procedure is to load the file to staging table and move on from
there.

--
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]