Help with INSERT query

I need to append records into a table with a two column primary key from a table that contains many records that already exist in the target table. How do I separate out the records in the source table that don't exist in the target?

When I used to do this in Access, I could write a simple append query that would automatically skip records in source that violated key constraints in the target. I'm trying to duplicate that funcionality.

Thanks.

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
WHERE Source.field NOT IN (SELECT Field FROM TARGET)

If you want to do a multi-field check...

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field = Source.Field
WHERE Target.field IS NULL

|||

Thanks so much for the quick reply.

I need to check both Source.field1 and Source.field2 against their counterparts in Target since it's the combination that provides the primary key in Target.

What I've tried that gets me a duplicate key error is

INSERT INTO Target (field1, field2)
SELECT field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field1 = Source.Field1 AND Target.field2 = Source.Field2
WHERE Target.field1 IS NULL AND Target.field2 IS NULL

It looks like it should work, but it doesn't.

Kato

|||

In your where clause, all you need to do is check the nullability of one field from the Target table. If the join fails, every field in your left table will be null.

If you get a duplicate key error, try using a SELECT DISTINCT instead of just a select.

If you still get the error, what's your primary key / unique index on in Target?