|
|
Posted by Erland Sommarskog on 09/30/78 11:16
(jennifer1970@hotmail.com) writes:
> Thanks for your assistance. You are always such a help! I did comment
> out the insert bit and did just a select. On the real tables I got
> 403300 records for 12/1/03. Doing just a select on the old Modifier
> table, without trying to leave out records that are not in the Detail
> table I got 403345 records. Checking the Modifiers production table,
> there are zero records for 12/1/03. I also got the same results as
> you when running my stored procedure on the test tables. I would not be
> surprised at all if there were duplicates in the source table. But
> wouldn't the NOT EXISTS part of the WHERE clause take care of that?
Yes, for the data that is already in the table prior to the insert. But
the NOT EXISTS is not re-evaluated once a row has been inserted. This
example demonstrates what I mean:
CREATE TABLE target (a int NOT NULL PRIMARY KEY)
go
CREATE TABLE source (a int NOT NULL)
go
INSERT source VALUES (98)
INSERT source VALUES (98)
go
INSERT target (a)
SELECT a FROM source s
WHERE NOT EXISTS (SELECT * FROM target t WHERE s.a = t.a)
go
DROP TABLE target, source
Just as a side note: more than I have been about going crazy when I have
run into a PK violation with an INSERT SELECT WHERE NOT EXISTS, because
I just have not been able to understand how the NOT EXISTS could fail.
Not until after some time, I have realized that the duplicates were in
the new rows, and I that I need a DISTINCT, better join conditions or
whatever.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|