Check Duplicates/Insert is failing

Question

Check Duplicates/Insert is failing

Dragan Knezevic

SSC Veteran

Points: 295
More actions
October 19, 2004 at 7:32 am

#162490

Hi,
I have the following problem. I need to check if a record from a query exists in the destination table. If exists then I need to update every record in destination table that matches every record in the query. If it doesn't exist then I need to insert all records from the query into the destination table. Could someone please tell me what is wrong with the following code:
--Check for duplicates
IF (NOT EXISTS(SELECT B.PartNumberId FROM tblInputOutputSchedule A RIGHT OUTER JOIN
  (SELECT
   PartNumberID, CONVERT(varchar(10), DateEntered, 101) AS DateEntered
   FROM tbl1701
   INNER JOIN tblPartNumbers ON tbl1701.PartNumber =
   tblPartNumbers.PartNumber
   GROUP BY PartNumberId, CONVERT(varchar(10), DateEntered, 101)
&nbsp B
  ON A.PartNumberId = B.PartNumberId AND
  CONVERT(VARCHAR(10), A.DateEntered, 101)= B.DateEntered))
--Records do not exist -> insert the query result
INSERT INTO tblInputOutputSchedule (PartNumberId, DateEntered,InputJB1701)
  SELECT
   PartNumberID,
   CONVERT(varchar(10), DateEntered, 101) AS DateEntered,
   SUM(CAST(Quantity AS DECIMAL(10, 2))) AS SumOfJB1701
   FROM dbo.tbl1701
   INNER JOIN tblPartNumbers ON tbl1701.PartNumber =
   tblPartNumbers.PartNumber
   GROUP BY PartNumberId, CONVERT(VARCHAR(10), DateEntered, 101)
ELSE
UPDATE tblInputOutputSchedule
  SET tblInputOutputSchedule.InputJB1701 = B.SumOfJB1701
FROM tblInputOutputSchedule A INNER JOIN
  (SELECT
   PartNumberID,
   CONVERT(varchar(10), DateEntered, 101) AS DateEntered,
   SUM(CAST(Quantity AS DECIMAL(10, 2))) AS SumOfJB1701
   FROM dbo.tbl1701
   INNER JOIN tblPartNumbers ON tbl1701.PartNumber =
   tblPartNumbers.PartNumber
   GROUP BY PartNumberId, CONVERT(VARCHAR(10), DateEntered, 101)
&nbsp B
ON A.PartNumberId = B.PartNumberId AND CONVERT(VARCHAR(10), A.DateEntered, 101)= B.DateEntered

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

Maybe you can explain what's wrong with the code? Are you getting an error? Is it doing the wrong thing? It's difficult to tell just by eyeballing it. Can you post DDL and sample data?

--
Adam Machanic
whoisactive

Dragan Knezevic SSC Veteran Points: 295 More actions · Answer 2

The problem is that it will not insert my records if NOT EXISTS is true and if for example I have one matching record in my destination table that one record will be updated but the remaining records from the query are not being inserted.

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

Can you post DDL and sample data to reproduce the problem?

http://www.aspfaq.com/etiquette.asp?id=5006

--
Adam Machanic
whoisactive

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

Why not just go ahead and do the update first, it will only affect existing records with matching PartNumberID.

Then insert the remaining records with something like this:

insert into table (...)

select tbl1.fld1, tbl1.fld2, ...

from tbl1

left join tbl2 on tbl1.pk = tbl2.pk

where tbl2.pk is null

Adam Machanic SSCoach Points: 15370 More actions · Answer 5

Scott,

For that purpose I usually do something along the lines of:

UPDATE Tbl

SET x = 'y'

WHERE x = 'abc'

IF @@ROWCOUNT = 0

INSERT Tbl (x) VALUES ('y')

... but in this case, I'm assuming there's some logic issue in the code itself.

--
Adam Machanic
whoisactive