473,320 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 4427
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Gary Harvey | last post by:
I have a data intensive program that requires all data to be present in memory. I keep running out of memory at about 2G whenever I run my program. I tried using a 64 bit version of Perl and hit...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
4
by: DEWright_CA | last post by:
I am trying to see if a file exists in a virtual directory, and if so run a method. I try doing File.Exists and the method runs but the file isn't there. Is there a web version of File.Exists or...
3
by: moondaddy | last post by:
I need to start an ms access 2003 app from a vb.net winforms app and didn't want to load all the interop stuff for office into the .net project. I thought that using the shell cmd would be a nice...
14
by: Ben | last post by:
I don't know whether anyone can help, but I have an odd problem. I have a PSP (Spyce) script that makes many calls to populate a database. They all work without any problem except for one...
0
by: b.coolsaet | last post by:
Hi, like the title says simplexml is giving me a "Node no longer exists" error while using the addChild() function. Note that it's not giving an error on the first addChild(); Can somebody...
0
by: clinnebur | last post by:
We have an ASP.NET web application (C#) that copies videos from a CCTV truck to a Linux server. What I am trying to do is convert the .AVI videos(which is how they are created on the truck) to .WMV...
4
by: qwedster | last post by:
Howdy folks! I am using stored procedure to see if a value exists in database table and return 0 if exists or else -1, in the following SQL queries. However how to check if a value (that is...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.