Bulk insert, skip rows with duplicate key error?

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?nano (nano@.nano.ono) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. I meant a regular sql operation. I will take a look at
your suggestion, it looks good. Access has another way of handling this
(non-sql) and while the syntax you suggest probably works in Access,
I've never tried it.