Access .adp :How to INSERT all but KEY violations

[JimJimJim]

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk-in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?


Jim

 

[Norman Yuan]

You may want to try this "INSERT INTO...SELECT..." in the SP:

INSET INTO tblTarget (....)
SELECT ... FROM tblSource WHERE tblSource.PKField NOT IN (SELECT
tblTarget.PKField FROM tblTarget)