Ignore Duplicate Key

[michael]

When doing a bulk insert to a table that has a Unique
Index, there is a property called "Ignore duplicate key".
This allows the bulk insert to complete even if there are
source records that have key fields already in the
destination table(of course these records are not added).

Is there a similar property that can be used with Primary
Key Constraints so that a bulk insert can complete even
if there are source records that have key fields already
in the destination table(again not adding these records).
This sure would be a convenient feature since now I need
to create a query to identify unique records before I
insert them into a table with a Primary Key Constraint.
MS Access allows you to do an "Append" ignoring records
that would create key violations. Why not in SQL Server
2000.

Michael

[John Bell]

Hi

I don't think there is a way to do what you want directly, you could
increase max errors, but they could be errors for other reasons.

The way that I would implement this is through a holding table that does not
have the primary key constraint. You can then delete any duplicate rows
before inserting them into the main data table.
As I would expect the main data table to have a significant number of rows
(compared to those loaded), this may also be faster.

John

"michael" <mic...@hockstein.org> wrote in message
news:006701c2ad09$30999e40$d5f82ecf@TK2MSFTNGXA12...

[Steve Thompson]

"michael" <mic...@hockstein.org> wrote in message
news:006701c2ad09$30999e40$d5f82ecf@TK2MSFTNGXA12...

No way to short circuit Primary Key constraints, other than dropping the PK,
loading your data, fixing the dup's then re-add the PK.

Steve

[michael]

Too bad. That's one of the nice things that I liked about
Access. I'm not sorry that I moved up, but I do like some
of the things that I had to leave behind.

Michael

>.
>

[Umachandar Jayachandran]

You can do this by using an INSTEAD OF trigger in SQL2000. Make sure
that the Bulk Insert fires the triggers for this to work. Here is an
example:

use tempdb
go
create table t1( i int primary key )
go
create trigger i_t1 on t1 instead of insert
as
begin
insert into t1
select distinct i from inserted
end
go
insert into t1
select 1
union all
select 1
go
select * from t1
go
drop table t1
go

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )