I would like to insert into table1 all the unique MFG,MAKE,MODEL fields from the union of 3 other tables.

table1 has MFG,MAKE,MODEL set as the primary key.

So I use INSERT INTO and UNION statements to store the values. This would work fine without primary key, but is there any way to specify ignoring PRIMARY KEY constraint errors while running a query?

If you are getting primary key constraint errors, it's because you are trying to insert rows with a duplicate key. While you could temporarily remove the columns as the primary key, you'd get the errors right back as soon as you tried to reestablish them as the key, with the added problem that now you've got multiple rows in the table with the same MFG,MAKE,MODEL combination.

By definition, the primary key must be unique.

Your INSERT query is generating duplicate values for some MFG,MAKE,MODEL combinations.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I am aware of the reason for the error...

What I would like to end up with is a table with all unique MFG, MAKE, MODEL combinations.

This can be done in ACCESS by appending to a empty table and ignoring the primary key violations. New records that violate the constraint are not appended...

What I'm trying to do is the same thing in SQL Server by creating a SP that will result in the same thing.

I must be missing something here, but if you are aware of the reason for the error, and you want a table with unique MFG, MAKE, MODEL combinations, then, have your query select unique values for your INSERT.

I suppose you could instead insert rows one at a time from within a cursor and ignore the constraint errors. You could also do the INSERT with no primary key defined, then remove the duplicates after the INSERT (this can be tricky, though), and then establish the primary key. But I know of no set-based way to do what you want.

I've heard it said from Relational purists that it can take over a year to recover from Access poisoning. :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com