John W. Colby
jwcolby at colbyconsulting.com
Thu Sep 23 09:05:23 CDT 2004
I created a NOT IN (SELECT...) and that does work. An outer join per Arthur's suggestion is marginally faster. In fact I have now succeeded in the operation using the outer join method. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike & Doris Manning Sent: Wednesday, September 22, 2004 9:52 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Violation of primary key constraint Take a look at the NOT IN topic in BOL. Doris Manning Database Administrator Hargrove Inc. www.hargroveinc.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Wednesday, September 22, 2004 8:56 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Violation of primary key constraint Well I just discovered the <> operator in the Join in SQL Server. I think this will solve my problem since I just back up and pull only the records with a ZIP in field B where the PK <> PK in the destination table. NO idea what implication this has for execution time though. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Wednesday, September 22, 2004 8:48 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Violation of primary key constraint I built a table that I want to dump records in to. The source data is the PK of my big table plus one of two different zip code fields. Two queries, all the records - PKs plus ZIP - from any records with a Zip in field A. The second all the records - PK plus Zip - with any Zip in field B. Append query A, append query B. In access, Query B would append all the records where there was not a collision with the PKs already in the table. In SQL Server the entire second query just fails because of a collision, giving me a "Violation of primary key constraint, statement terminated". As Bill Cosby says in one of his wonderful acts, "brain damaged children". 'Scuse me, I WANT the primary key constraint to prevent records from going in but I also want those records without a violation to go in. So how do I override this brain damaged child and tell it to accept those records that do not violate the PK constraint? John W. Colby www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com