Insert problems for Rudy (or anyone else)

[dickiebird]

Hello again
Another problem's occurred - that perhaps you can help with
Those 5 rows are now found OK - thanks, but I want to insert new rows based on those found rows. I am trying to
increment TransactionID as below - but it returns :
" Attempt to insert duplicate key row in object 'Transactions' with unique index 'PK_TRANSACTIONS' "
(I assume it's that that is the problem - TransactionID is part of primary key, along with a timestamp and an identity column)


insert Transactions(
TransactionID,
TillID,
SiteID,
CardNumber,
SettlementValue,
SettlementCurrency,
DateImported

select
1 + (select max(TransactionID) from Transactions where TransactionSourceId = 2),
99,
"1045",
lct.Cardnumber,
lct.SettlementValue,
lct.SettlementCurrency,
getdate
from Transactions lct, MemberPromotion ba
where ba.CardNumber = lct.CardNumber
and ba.PointsAwarded =0
and lct.CardTypeId = "BA"
and lct.SettlementValue > 400
and lct.SettlementCurrency = "GBP"
and lct.TranDate = (select min(TranDate)
from Transactions
where CardNumber = lct.CardNumber
and CardTypeId = "BA"
and TranDate between "20 Jan 2004" and "1 Jul 2004"
and SettlementValue > 400
and SettlementCurrency = "GBP&quot

How can I obtain the increment of the TransactionID for each new row ?

Your help is appreciated
TIA

Dickie Bird (-)))

 

[dickiebird]

Ha-Ha - I'd worry too - but it's single-user processing of incoming flat files onto a database that is only activated once a day long after the above is run in !
Have I got to use a cursor ?

Dickie Bird (-)))

 

[r937]

run the SELECT separately

examine the very first column

notice anything weird?



rudy

http://r937.com/

SQL Consulting

 

[dickiebird]

yes - the transaction numbers are all the same - point proved, thanks.
Any ideas ?

Dickie Bird (-)))

 

[r937]

ideas?

use an IDENTITY/AUTONUMBER/AUTO_INCREMENT/SEQUENCE field

rudy

http://r937.com/

SQL Consulting

 

[rrrkrishnan]

If it is oracle, you can use rownum.

 

[r937]

yeah, you can use rownum, so that the block of new rows is numbered 1, 2, 3, ...

but what happens when you try inserting another block?



rudy

http://r937.com/

SQL Consulting

 

[r937]

oh, wait, of course..

(select max(TransactionID) from Transactions where TransactionSourceId = 2) + rownum

sorry




rudy

http://r937.com/

SQL Consulting