SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Issue inserting records based on a calculation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 11/27/2012 :  11:18:52  Show Profile  Reply with Quote
Hi All
I have been using the below sql to insert n number of records into a table(Renoffcyc).I derive n based on subtracting two datetime feilds in a table. For each record inserted into the Renoffcyc table i have to increment the requestid in a table called table_keys.We do this because we have differant web forms which fetch the enxt requestid from this table_keys table.

I dont know why but sometimes the SQL works perfect and sometimes i get an error and unable to understand why this might be happening

Msg 2627, Level 14, State 1, Line 42
Violation of PRIMARY KEY constraint 'PK__RENOFFCYC__464936E5'. Cannot insert duplicate key in object 'dbo.renoffcyc'.
The statement has been terminated.


SQL

SET NOCOUNT ON

GO 

--declare @reqno int
--set @reqno = 231675
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int

 
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)

DECLARE db_cursor CURSOR FOR 
SELECT Requestid from report_request where requestid in (149142)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @oldrequestid


WHILE @@FETCH_STATUS = 0   
BEGIN  
     
     --do work here    
     
SET @num_of_times = NULL
select @num_of_times=datediff(month, receiveddate,expirationdate) from report_request where requestid in (@oldrequestid) 
SET @num_of_times1 = @num_of_times+1




set @count=0
WHILE @count < @num_of_times1 
BEGIN
     
update table_keys
set key_id = key_id + 1 
where table_name = 'adhoc'

Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

     
INSERT INTO [UnderWriting].[dbo].[renoffcyc]
           ([roc_id]
           ,[roc_ind]
           ,[fundtype]
           ,[prodmth]
           ,[opa_id]
           ,[hlthplnid]
           ,[targetdt]
           ,[actualdt]
           ,[freqcd]
           ,[Comments]
           ,[entereddt]
           ,[pltfrmind]
           ,[groupcnt]
           ,[rptdesc]
            ,[rptrsrcetxt]
           ,[tat]        
           ,[mktsgnm]
           ,[date_available]
            ,[requestorid]
           ,[groupname]
           ,[groupnbr]
           ,[tool]
           ,[datarecpdet]
           ,[HRS]
           ,[mins]
           ,[busneed]
           ,[origrequestid]
           )
  
           
SELECT @newrequestid
      ,'O'
       ,RR.fundingtype
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,SR.Assignto
           ,RR.hlthplnid
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))

           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,RR.defineschedule
           ,RR.rptcomments
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
			,SR.pltfmcd
			,SR.groupcnt
			,SR.rptdesc
			,SR.resource
           ,SR.tat
           ,mktsg
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,RR.requestor
           ,RR.CTNAME
           ,RR.MSGN
           ,SR.tool
           ,SR.datarecpdet
           ,SR.hrs
           ,SR.mins
           ,RR.busneed
           ,@oldrequestid
   

             
FROM [UnderWriting].[dbo].[REPORT_REQUEST] RR FULL OUTER JOIN SELECTED_REPORT SR
ON RR.requestid = SR.requestid
where RR.requestid = @oldrequestid



set @count=@count+1 
END

FETCH NEXT FROM db_cursor INTO @oldrequestid
END  -- Cursor loop
CLOSE db_cursor
DEALLOCATE db_cursor



Example
I feed a requestid and i know that there will be 13 records that need to be inserted into the renoffcyc table.when i run the query i see that the requestid is incremented 13 times in the table_keys table but it failed with the above error

Need help on this please

thanks

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 11/27/2012 :  11:46:00  Show Profile  Reply with Quote
At a minimum, you'll want to put a transaction around the table_keys update/select statements.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 11/27/2012 :  12:57:43  Show Profile  Reply with Quote
but will that fix the issue on why it is erroring out inserting records into the table
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 11/27/2012 :  14:56:30  Show Profile  Reply with Quote
It sounds to me that another process is trying to insert records into the table. There is a window of (bad) opportunity when both processes are accessing the table_keys table and they end up retreiving the same value. The transaction would act to serialize the two processes so that the first would update and select before the second could gain access to the table/row.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 11/28/2012 :  11:58:53  Show Profile  Reply with Quote
I have been using this in my local development environment and sure that no porcess other than this are using to update the requestid

Iam at a loss on this now

quote:
Originally posted by Bustaz Kool

It sounds to me that another process is trying to insert records into the table. There is a window of (bad) opportunity when both processes are accessing the table_keys table and they end up retreiving the same value. The transaction would act to serialize the two processes so that the first would update and select before the second could gain access to the table/row.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4360 Posts

Posted - 11/28/2012 :  12:15:54  Show Profile  Reply with Quote
What is the definition of the PK (PK__RENOFFCYC__464936E5)?
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 11/28/2012 :  14:13:16  Show Profile  Reply with Quote
PRIMARY KEY CLUSTERED
(
[roc_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


quote:
Originally posted by Lamprey

What is the definition of the PK (PK__RENOFFCYC__464936E5)?

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4360 Posts

Posted - 11/28/2012 :  16:26:41  Show Profile  Reply with Quote
Well you are doing a FULL OUTER JOIN which, apparently, is producing more than one row. I don't know how you would determine which row your are interested in. But, if you don't care which one, then you could just add a TOP 1 clause to the SELECT. Otherwise, you'll have to figure out how rank the rows and only use one or change the logic so you can insert multiple rows.
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 12/04/2012 :  14:39:01  Show Profile  Reply with Quote
Thanks the outer join was the issue as there were few requests where i was getting multiple records from SELECTED_REPORT table


how do i change this logic such that if there are multiple records for a request in SELECTED_REPORT table then i need to insert n(is a calculated feild in my logic) number of records for each record in SELECTED_REPORT table. rptdesc is the key name in selected_report table

so for example
n =10
number of records in selected_report table = 2

total number of records that need to be inserted are (n+1)*2 = 22

thanks

quote:
Originally posted by Lamprey

Well you are doing a FULL OUTER JOIN which, apparently, is producing more than one row. I don't know how you would determine which row your are interested in. But, if you don't care which one, then you could just add a TOP 1 clause to the SELECT. Otherwise, you'll have to figure out how rank the rows and only use one or change the logic so you can insert multiple rows.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000