Re: Violation of primary key

Thank you for the pointers. It was indeed the source table that
contained the duplicates.

As far as using NOT EXISTS vs. the Left Outer Join syntax...isn't this
more a matter of style than correctness? In a Left Outer Join the two
fields in the destination would be NULL as the source records are not
found in the destination table. I understand NOT EXISTS also works,
but I'm inclined to believe that using JOINs are more efficient.

On Mar 28, 4:26 pm, Erland Sommarskog wrote:
>
> No.
>
> > This sql statement false due to the primary key violation:
>
> > Insert Into MHGROUP.Custom2
> > Select
> > ClientNumber,
> > MatterNumber,
> > MatterDescription,
> > 'Y'
> > From MG_EliteMatters EM
> > Left Outer Join MHGROUP.Custom2 C2
> > On C2.CPARENT_ALIAS = EM.ClientNumber
> > And C2.Custom_ALIAS = EM.MatterNumber
> > Where CPARENT_ALIAS Is Null And Custom_ALIAS Is Null
>
> I assume this is supposed to be a query like:
>
> INSERT target (...)
> SELECT ...
> FROM source s
> WHERE NOT EXISTS (SELECT *
> FROM target t
> WHERE t.pk = s.col)
>
> (Hint: if this is what you mean, write the query in that way to express
> it. Makes your code more maintenable.)
>
> That is, insert all rows in source where the is not alreay is an existing
> row in target.
>
> When such query fails, it's usually because there are duplicates in the
> source data. That is there are two rows in soruce with the same
> (ClientNumber, MatterNumber) and this tuple does not exist in Target.
>
> By the way, shouldn't you have a condition
>
> AND EM.ClientNumber IS NOT NULL
> AND EM.MatterNumber IS NOT NULL
>
> as these columns are nullable?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: Violation of primary key

Zamdrist (zamdrist@gmail.com) writes:
> As far as using NOT EXISTS vs. the Left Outer Join syntax...isn't this
> more a matter of style than correctness? In a Left Outer Join the two
> fields in the destination would be NULL as the source records are not
> found in the destination table. I understand NOT EXISTS also works,
> but I'm inclined to believe that using JOINs are more efficient.

It's indeed matter of style and expressiveness than correctness.

As for efficient, you can never tell before you benchmark the query at hand.
If the optimizer does it right, you should get the same plan in both cases
anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx