Is there a method to determine the value of the duplicate key when the error triggered?
(in this example, DuplicateKeyValue)
If not, is there a better way to perform the following?

Importing new inventory into existing inventory, adding new, update qty on existing.

Regards,
Scott.

// primary index on StockSerial

 with qryTemp do begin
   if Active then Close;
   SQL.Clear;
   SQL.Text := 'INSERT INTO Inventory SELECT * FROM SWFY';
   try
     ExecSQL;
     Close;
   except
   on E: Exception do begin
     // error 9729 duplicate key found  , DBISAM_KEYVIOL
     SQL.Clear;
     SQL.Text := 'UPDATE Inventory SET Qty = 2 WHERE StockSerial = '+QuotedStr(DuplicateKeyValue);
     try
       ExecSQL;
       Close;
     except
     end;
   end;
   end;
 end;

Scott,

<< Is there a method to determine the value of the duplicate key when the
error triggered? (in this example, DuplicateKeyValue) >>

Unfortunately, no.  What is needed is an INSERT BUT UPDATE IF DUPLICATE type
of SQL construct added to the basic INSERT statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

That is what I read somewhere, but was not sure.

Can I make a request for this? huh, huh, please, please ....
seems to be a useful option.

Regards,
Scott.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:794B71F6-2B9D-422D-B221-CEBE9D076426@news.elevatesoft.com...
> Scott,
>
> << Is there a method to determine the value of the duplicate key when the error triggered? (in this example,
> DuplicateKeyValue) >>
>
> Unfortunately, no.  What is needed is an INSERT BUT UPDATE IF DUPLICATE type of SQL construct added to the basic
> INSERT statement.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Scott


Shouldn't you check to see if it exists BEFORE trying to insert it?

Roy Lambert

"Scott Martin" <scottmartin@pdq.net> wrote in
news:AA77D635-CFF0-42E2-BA23-4E59BAC787A6@news.elevatesoft.com:

>   with qryTemp do begin
>     if Active then Close;
>     SQL.Clear;
>     SQL.Text := 'INSERT INTO Inventory SELECT * FROM SWFY';
>     try
>       ExecSQL;
>       Close;
>     except
>     on E: Exception do begin
>       // error 9729 duplicate key found  , DBISAM_KEYVIOL
>       SQL.Clear;
>       SQL.Text := 'UPDATE Inventory SET Qty = 2 WHERE StockSerial =
>       '+QuotedStr(DuplicateKeyValue); try
>         ExecSQL;
>         Close;
>       except
>       end;
>     end;
>     end;
>   end;
>
>

You could try something like:
-------------------------------8<---------------------------
SELECT I.StockSerial aSerial, S.Qty + I.Qty aQty
INTO memory\tmpTbl
FROM Swfy S, Inventory I
WHERE S.StockSerial = I.StockSerial
;

UPDATE Inventory SET Qty = aQty
FROM Inventory
JOIN memory\tmpTbl ON StockSerial = aSerial
;

DELETE FROM Swfy
WHERE StockSerial IN (SELECT aSerial FROM memory\tmpTbl)
;

DROP TABLE memory\tmpTbl
;

INSERT INTO Inventory SELECT * FROM SWFY
;

-------------------------------8<---------------------------

--
Chris

Roy,

Now there is a thought ....;p

In the old version, using tables, I used a do while loop and findkey, worked as expected.

The new version is all SQL based, so I was hoping for a DUPLICATE contruct as Tim mentioned
so I could issue a basic SQL statement.

I see chris has provided a workaround using a memory file.

Regards,
Scott.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:DB90FA71-A698-4350-A8EA-305259ED7AD0@news.elevatesoft.com...
> Scott
>
>
> Shouldn't you check to see if it exists BEFORE trying to insert it?
>
> Roy Lambert
>

Chris,

Thanks, I will give it a run.

Regards,
Scott.

"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message news:Xns979074143207614torcatis@64.65.248.118...

> You could try something like:

Scott, why don't you write it all in SQL versus interating the dataset.

Insert all records that there is no match on primary key. (new Records)
Then .. update the rest or update them all ...

Insert into Target
Select s.* from Source
Left join target t on s.pk = t.pk
where t.pk is null;

Then run your update statement for all the record of delete
the ones that you just insert from your source .. whatever makes sense ..

Another option .. Donat.