primary key violation-- need to insert new record error

[EBee]

I am trying to insert records in the RateIncrease table for all subscriptionid.

I need to insert the subscriptionid only one time. my source table (Transactions) have many transactions for one subscriptionID. I only need to grab the first payment after 7/31/05, and insert it into the RateIncrease table. If the subscriptionid is in the RateIncrease Table, I don't want to insert another record for that subscriptionid.

The problem I have is that the second pass, it seems to be getting the same subscriptionid and trying to insert it again. How can I prevent the subscriptionid from the rateincrease table from getting selected again.


select
trn.SubscriptionID
, TranDate = min(trn.TranDate)
, CreateDate = min(trn.CreateDate)

into #FirstPayment

FROM Transactions trn, RateIncrease ri
where trn.paymentamount > 0 and
trn.trandate > '07/25/2005' and
trn.paymentRateCode in
( 'DSI'
, 'SMI'
, 'DSO'
, 'MonthDSI'
, 'SMO'
, 'MonthSMI'
, 'MonthDSO'
, 'MonthSUI'
, 'MonthSMO'
, 'MonthSUO') and

ri.Subscriptionid <> trn.SubscriptionID
group by trn.subscriptionID

select * from #FirstPayment
order by subscriptionid

insert into RateIncrease
SELECT
trn.SubscriptionID
, trn.PaymentRateCode
, trn.Paymentamount
, trn.TranDate
, pay.CreateDate

FROM Transactions trn
inner join #FirstPayment pay ON
pay.SubscriptionID = trn.SubscriptionID and
pay.CreateDate = trn.CreateDate
left outer join RateIncrease ri ON
trn.SubscriptionID = ri.SubscriptionID and
ri.CreateDate = pay.CreateDate

where
trn.paymentRateCode in
( 'DSI'
, 'SMI'
, 'DSO'
, 'MonthDSI'
, 'SMO'
, 'MonthSMI'
, 'MonthDSO'
, 'MonthSUI'
, 'MonthSMO'
, 'MonthSUO') AND

ri.subscriptionid is null


Drop table #FirstPayment
GO

 

[amrita418]

Do the following changes and see what you get:
Update the first select stmt to this:

select 		trn.SubscriptionID,    
		TranDate = min(trn.TranDate),    
		CreateDate =  min(trn.CreateDate)
into 		#FirstPayment
FROM     	Transactions trn, RateIncrease ri
where     	trn.paymentamount > 0 
		and trn.trandate > '07/25/2005' 
		and trn.paymentRateCode in ('DSI', 'SMI', 'DSO', 'MonthDSI', 'SMO',  
                                            'MonthSMI', 'MonthDSO', 'MonthSUI', 'MonthSMO', 'MonthSUO') 
                and trn.SubscriptionID not in (select 	distinct 
							Subscriptionid   
					       from 	RateIncrease) 
group by 	trn.subscriptionID

Delete all references to the RateInterest table in the
insert into RateIncrease
select blah blah from ... (just use Trn and Temp Table).

Let us know your findings.

Regards,
AA

 

[amrita418]

Forgot to remove ",RateIncrease ri" from the first update stmt.

Regards,
AA

 

[EBee]

This is what I have and getting error msg:
Server: Msg 213, Level 16, State 4, Line 24
Insert Error: Column name or number of supplied values does not match table definition.
======================================================
select trn.SubscriptionID,
TranDate = min(trn.TranDate),
CreateDate = min(trn.CreateDate)
into #FirstPayment
FROM Transactions trn
where trn.paymentamount > 0
and trn.trandate > '07/31/2005'
and trn.paymentRateCode in ('DSI', 'SMI', 'DSO', 'MonthDSI', 'SMO',
'MonthSMI', 'MonthDSO', 'MonthSUI', 'MonthSMO', 'MonthSUO')
and trn.SubscriptionID not in (select distinct
ri.Subscriptionid
from RateIncrease ri)
group by trn.subscriptionID

insert into RateIncrease
SELECT
trn.SubscriptionID
, trn.PaymentRateCode
, trn.Paymentamount
, pay.TranDate

FROM Transactions trn
inner join #FirstPayment pay ON
pay.SubscriptionID = trn.SubscriptionID and
pay.TranDate = trn.TranDate

where
trn.paymentRateCode in
( 'DSI'
, 'SMI'
, 'DSO'
, 'MonthDSI'
, 'SMO'
, 'MonthSMI'
, 'MonthDSO'
, 'MonthSUI'
, 'MonthSMO'
, 'MonthSUO')


Drop table #FirstPayment
GO

 

[EBee]

I re-run it again and got the same error that I have been getting. Still trying to insert duplicate subsctiptionid

ERROR:
(3177 row(s) affected)

Server: Msg 2627, Level 14, State 1, Line 24
Violation of PRIMARY KEY constraint 'RateInc_prikey'. Cannot insert duplicate key in object 'RateIncrease'.
The statement has been terminated.


===========================================
select trn.SubscriptionID,
TranDate = min(trn.TranDate),
CreateDate = min(trn.CreateDate)
into #FirstPayment
FROM Transactions trn
where trn.paymentamount > 0
and trn.trandate > '07/31/2005'
and trn.paymentRateCode in ('DSI', 'SMI', 'DSO', 'MonthDSI', 'SMO',
'MonthSMI', 'MonthDSO', 'MonthSUI', 'MonthSMO', 'MonthSUO')
and trn.SubscriptionID not in (select distinct
ri.Subscriptionid
from RateIncrease ri)
group by trn.subscriptionID

insert into RateIncrease
SELECT
trn.SubscriptionID
, trn.PaymentRateCode
, trn.Paymentamount
, pay.TranDate
, pay.CreateDate

FROM Transactions trn
inner join #FirstPayment pay ON
pay.SubscriptionID = trn.SubscriptionID and
pay.TranDate = trn.TranDate

where
trn.paymentRateCode in
( 'DSI'
, 'SMI'
, 'DSO'
, 'MonthDSI'
, 'SMO'
, 'MonthSMI'
, 'MonthDSO'
, 'MonthSUI'
, 'MonthSMO'
, 'MonthSUO')


Drop table #FirstPayment
GO

 

[amrita418]

Try this:

Insert into RateIncrease
Select    A.SubscriptionID,
          A.PaymentRateCode,
          A.PaymentAmount,          
          A.TranDate,    
          A.CreateDate
From      Transactions A,
          (Select    SubscriptionId,
                     Min(TranDate) 'MinTranDate'
                     From      Transactions
                     Where     TranDate > '07/31/2005'
                     Group By  SubscriptionId) B
Where     A.SubscriptionId = = B.SubscriptionId
          and A.transactiondate = b.MintranDate
          and A.paymentamount > 0
          and A.paymentRateCode in('DSI', 'SMI', 'DSO', 'MonthDSI', 'SMO', 
                                   'MonthSMI', 'MonthDSO', 'MonthSUI', 
                                   'MonthSMO', 'MonthSUO'))
          and A.SubscriptionID not in (Select  Distinct
                                               SubscriptionId   
                                       From    RateIncrease)

Clearly it seems that I was sleeping when I responded to your post last night. Let me know if this helps.

Regards,
AA

 

[amrita418]

Forgot to mentio that you do not need temp table or two separate stmts to do the insert. The stmt above is all you need. There might be some syntactical errors, make sure those are taken care of.

Regards,
AA

 

[EBee]

ERROR:
Executed as user: SACBEE\SBDEVSQLSERV. Violation of PRIMARY KEY constraint 'RateInc_prikey'. Cannot insert duplicate key in object 'RateIncrease'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

==========================================================

Create procedure UpdateRateIncrease
AS
Insert into RateIncrease
Select A.SubscriptionID,
A.PaymentRateCode,
A.PaymentAmount,
A.TranDate,
A.CreateDate
From Transactions A,
(Select SubscriptionId,
Min(TranDate) 'MinTranDate'
From Transactions
Where TranDate > '07/31/2005'
Group By SubscriptionId) B
Where A.SubscriptionId = B.SubscriptionId
and A.trandate = b.MintranDate
and A.paymentamount > 0
and A.paymentRateCode in('DSI', 'SMI', 'DSO', 'MonthDSI', 'SMO',
'MonthSMI', 'MonthDSO', 'MonthSUI',
'MonthSMO', 'MonthSUO')
and A.SubscriptionID not in (Select Distinct
SubscriptionId
From RateIncrease)