469,330 Members | 1,373 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Using EXISTS

I need to insert records into the table parSalesDetailModifier from
OLDparSalesDetailModifier where (1) those records DO NOT exit in
parSalesDetailModifier and (2) those records have a parent record in
parSalesDetail.

When I run the below query I get the error message that I am violating
the Primary Key Constraint for parSalesDetailModifier. In other words,
it's trying to insert a record that does exist.

Also posted below are create and insert startements for thte tables.

If someone would be kind enough to show me what I am doing wrong, I'd
really appreciate it.

Thanks,
Jennifer

-------------------------------
- STORED PROCEDURE
-------------------------------
CREATE Proc LoadModifier2

@S datetime,
@E datetime

AS

INSERT INTO ParSalesDetailModifier
(
parSalesDetailModifierID,
parSalesHdrID,
parSalesDetailID,
ModifierType,
POSModifier,
Condiment,
CondimentPrice,
UnitNumber,
BusinessDay
)

SELECT
OLD.parSalesDetailModifierID,
OLD.parSalesHdrID,
OLD.parSalesDetailID,
OLD.ModifierType,
OLD.POSModifier,
OLD.Condiment,
OLD.CondimentPrice,
OLD.UnitNumber,
OLD.BusinessDay

FROM OldParSalesDetailModifier OLD

WHERE

EXISTS
( SELECT DET.parSalesHdrID,
DET.parSalesDetailID,
DET.UnitNumber,
DET.BusinessDay
FROM ParSalesDetail DET
WHERE OLD.parSalesHdrID = DET.parSalesHdrID AND
OLD.parSalesDetailID = DET.parSalesDetailID AND
OLD.UnitNumber = DET.UnitNumber AND
OLD.BusinessDay = DET.BusinessDay
)

AND

NOT EXISTS
( SELECT NEW.parSalesHdrID,
NEW.parSalesDetailID,
NEW.parSalesDetailModifierID,
NEW.UnitNumber,
NEW.BusinessDay
FROM ParSalesDetailModifier NEW
WHERE OLD.parSalesHdrID = NEW.parSalesHdrID AND
OLD.parSalesDetailID = NEW.parSalesDetailID AND
OLD.parSalesDetailModifierID = NEW.parSalesDetailModifierID AND
OLD.UnitNumber = NEW.UnitNumber AND
OLD.BusinessDay = NEW.BusinessDay
)

AND OLD.BusinessDay between @S and @E

-------------------------------
- END STORED PROCEDURE
-------------------------------
-------------------------------
- CREATE TABLES
-------------------------------

CREATE TABLE [parSalesDetailModifier] (
[ParSalesDetailModifierID] [int] NOT NULL ,
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[ModifierTYPE] [int] NULL ,
[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CondimentPrice] [money] NOT NULL ,
[UnitNumber] [int] NOT NULL ,
[BusinessDay] [datetime] NOT NULL ,
CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID],
[ParSalesDetailModifierID]
) WITH FILLFACTOR = 70 ON [PRIMARY] ,
CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
) REFERENCES [parSalesDetail] (
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
)
) ON [PRIMARY]
GO

CREATE TABLE [OLDparSalesDetailModifier] (
[ParSalesDetailModifierID] [int] NOT NULL ,
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[ModifierTYPE] [int] NULL ,
[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CondimentPrice] [money] NOT NULL ,
[UnitNumber] [int] NOT NULL ,
[BusinessDay] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [parSalesDetail] (
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[Before] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[After] [int] NOT NULL ,
[Promo] [money] NOT NULL ,
[PromoBefore] [money] NOT NULL ,
[ItemPrice] [money] NOT NULL ,
[PromoAfter] [money] NOT NULL ,
[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitNumber] [int] NOT NULL ,
[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmountTenderTime] [datetime] NULL ,
[BusinessDay] [datetime] NOT NULL ,
CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
) WITH FILLFACTOR = 70 ON [PRIMARY] ,
CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID]
) REFERENCES [parSalesHdr] (
[BusinessDay],
[UnitNumber],
[parSalesHdrID]
)
) ON [PRIMARY]
GO

-------------------------------
- END CREATE TABLES
-------------------------------

-------------------------------
- INSERT INTO TABLES
-------------------------------

insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')

insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NO
ONIN','NO ONIN',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')

insert into parSalesDetail
values(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10917341,0,1, 0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928910,0,1, 0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928911,0,1, 0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-01
10:09:44.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928912,0,1, 0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-01
10:09:44.000','2003-12-01')
insert into parSalesDetail
values(2298561,10929376,0,1, 0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-01
10:00:44.000','2003-12-01')
-------------------------------
- END INSERT INTO TABLES
-------------------------------

Jul 23 '05 #1
7 4320
(je**********@hotmail.com) writes:
I need to insert records into the table parSalesDetailModifier from
OLDparSalesDetailModifier where (1) those records DO NOT exit in
parSalesDetailModifier and (2) those records have a parent record in
parSalesDetail.

When I run the below query I get the error message that I am violating
the Primary Key Constraint for parSalesDetailModifier. In other words,
it's trying to insert a record that does exist.


Not necessarily. It may be that among the rows that does not exist in
the target table, there are duplicaates with regards to the primary key.
I notice that the source table does not have any primary-key constraint,
so duplicates cannot be excluded.

You could comment away the INSERT part, so that all you have is the
SELECT statement to inspect the output.

I ran your repro, but I did not get any index violation.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Erland,

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?

Thanks!
Jennifer

Jul 23 '05 #3
(je**********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Erland,

You were absolutely right - there were duplicates, which brings me to
another question. The thing is, the duplicates are not *really*
duplicates. There is a field called parSalesDetailModifierID, which
should be unique for each modifier to the detail record.

For example, the table could have 2 records that modfiy one detail
record. One would have a ParSalesDetailModifierID of 1 and the other
would be 2. But there are some cases where the
ParSalesDetailModifierID is 1 for both cases. But the condiment may be
different - one may be to add cheese and the other may be add a patty.
We really do need to keep both records.

So now it is my job to find these offending records and renumber the
ParSalesDetailModifierID.

I thought I had it worked out. And I am almost positive that once I
write it all down here I'll see where I am going wrong. :) I thought
I'd use a cursor and loop through the repeaters and renumber them.
BUT.... I never seem to get out of the second cursor loop. It hangs
there and keeps renumbering the first record over and over again. It
looks like @@FETCH_STATUS is always zero.

Any ideas?

Thanks,
Jennifer

Declare @BusDay datetime
Declare @Unit int
Declare @HdrID int
Declare @DetID int

Declare @BusDay2 datetime
Declare @Unit2 int
Declare @HdrID2 int
Declare @DetID2 int
Declare @ModID2 int
Declare @POSModifier varchar(20)
Declare @Condiment varchar(20)
Declare @X int

-- CREATE THE FIRST CURSOR

DECLARE Mods CURSOR FOR
select distinct businessday, unitnumber, parsaleshdrid,
parsalesdetailid
from oldParSalesDetailModifier
where BusinessDay = '12/1/03' and unitnumber = 2 --and ParSalesHdrID =
2298561 and ParSalesDetailID = 10917332
group by businessday, unitnumber, parsaleshdrid, parsalesdetailid
HAVING Count(*) > 1
order by businessday, unitnumber, parsaleshdrid, parsalesdetailid

--OPEN THE FIRST CURSOR
OPEN Mods

--FETCH FIRST FROM FIRST CURSOR
FETCH NEXT FROM Mods into @BusDay, @Unit, @HdrID, @DetID

--BEGIN LOOP FOR FIRST CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
-- CREATE SECOND CURSOR
DECLARE Mods2 CURSOR FOR
SELECT BusinessDay, UnitNumber, parSalesHdrID, parSalesDetailID,
parSalesDetailModifierID, POSModifier, Condiment
FROM oldParSalesDetailModifier
WHERE BusinessDay = @BusDay and UnitNumber = @Unit and
ParSalesHdrID = @HdrID and ParSalesDetailID = @DetID

-- MAKE SURE X STARTS OUT AS 1
Set @X = 1

-- OPEN THE SECOND CURSOR
OPEN Mods2

-- FETCH FIRST FROM THE SECOND CURSOR
FETCH NEXT FROM Mods2 into @BusDay2, @Unit2, @HdrID2, @DetID2,
@ModID2, @POSModifier, @Condiment

--BEGIN LOOP FOR SECOND CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
-- UPDATE MODIFIER TABLE
UPDATE oldParSalesDetailModifier
SET parSalesDetailModifierID = @X

--SELECT @X, * from oldParSalesDetailModifier
WHERE BusinessDay = @BusDay and UnitNUmber = @Unit and
ParSalesHdrID = @HdrID and ParSalesDetailID = @DetID and
parSalesDetailModifierID = @ModID2 and POSModifier = @POSModifier
and
Condiment = @Condiment
print 'X: ' + Cast(@X as nvarchar(20)) + ' Unit: ' + Cast(@Unit as
nvarchar(20)) + ' HdrID: ' + Cast(@HdrID as nvarchar(20)) + ' DetID: '
+ Cast(@DetID as nvarchar(20)) + ' ModID: ' + Cast(@ModID2 as
nvarchar(20)) + ' Modifier: ' + @POSModifier + ' Condiment ' +
@Condiment

-- INCREMENT X
Set @X = @X + 1

-- FETCH NEXT FROM SECOND CURSOR
FETCH NEXT FROM Mods2 into @BusDay2, @Unit2, @HdrID2, @DetID2,
@ModID2, @POSModifier, @Condiment
END

-- CLOSE AND DEALLAOCATE SECOND CURSOR
CLOSE Mods2
DEALLOCATE Mods2

-- FETCH NEXT RECORD FROM FIRST CURSOR
FETCH NEXT FROM Mods into @BusDay, @Unit, @HdrID, @DetID
END

-- CLOSE AND DEALLOCATE THE FIRST CURSOR
CLOSE Mods
DEALLOCATE Mods

Jul 23 '05 #5
(je**********@hotmail.com) writes:
I thought I had it worked out. And I am almost positive that once I
write it all down here I'll see where I am going wrong. :) I thought
I'd use a cursor and loop through the repeaters and renumber them.
BUT.... I never seem to get out of the second cursor loop. It hangs
there and keeps renumbering the first record over and over again. It
looks like @@FETCH_STATUS is always zero.

Any ideas?


Try adding INSENSITIVE before CURSOR. That finalizes the result set for
the cursor once for all. The default keyset-driven cursors may pick
up your updates and feed you back the row you just changed.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Erland,

You are totally awesome! That did the trick!

Thanks!!!
Jennifer

Jul 23 '05 #7
(je**********@hotmail.com) writes:
You are totally awesome! That did the trick!


Glad to hear that it worked out!

I've gone to the point that I always use INSENSITIVE for cursors. I have
had so many problems with the default keyset-driven cursors. Not so much
funky results, as truly absymal query plans.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Gary Harvey | last post: by
4 posts views Thread by DEWright_CA | last post: by
reply views Thread by b.coolsaet | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.