Append query in SQL Server, PK error

[Mark CHP IMD]

I’m trying to run an append query from a stored procedure which fails. I’m
moving data from one table that has duplications on the primary key of the
second table. With Access, an error will be generated saying that ‘cannot
insert so many records because of key violations, do you want to continue
anyway’. Answer yes and the data is moved save any duplications in the PK. As
a SP, I get the PK error or another error if I set the Identity property on
the field to YES. Is there a way to run this kind of SQL knowing that not all
the records can be appended?
Access 2002
SQL Server 2000
Thanks,
Mark

 

[Michel Walsh]

With Jet, when you append many rows, in one 'batch', if a UNIQUE constraint
would fail, only the new records that would create the duplicated value are
NOT appended, while other records will be appended (unless you abort the
whole transaction).

With MS SQL Server, as soon as one record would create a duplicated value,
by default, the whole transaction is aborted. You can, with MS SQL Server,
do what you get with Jet if you use the IGNORE_DUP_KEY = ON when you create
the index (for the UNIQUE constraint) with that option. The exact syntax
depends on your MS SQL Server, see the BOL or other documentation at your
disposal. When that option is set, appending many rows will behave as JET as
far as UNIQUE constraint goes. There is no provision, in MS SQL Server, for
the other 'error' that Jet allow to by pass selectively, though.



Vanderghast, Access MVP