Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inserting multiple records based on a single recor

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 09:19:22
Hi everyone
I have been using the below logic to insert a single record from one table(CustomerInfo) into another table(Customerrenewal).How can i use the same logic to insert n number of records into customerrenewal table from customerinfo table based on calculating diffrence of months between two datetime feilds in the customerinfo table

the number of times n should be calculated based on the below

select datediff(month, orgeffdt,nxtrnwldt) from customerinfo
where requetno in (111111
)


inserting a record logic
SET NOCOUNT ON
GO 


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

DECLARE db_cursor CURSOR FOR 
SELECT Reqno from customerinfo where reqno in (111111)


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @oldrequestid
update requesttable
set key_id = key_id + 1 
where table_name = 'adhocrequests'
Select @newrequestid = key_id from requesttable where table_name = 'adhocrequests'


WHILE @@FETCH_STATUS = 0   
BEGIN  
     
     --do work here    
    
     
INSERT INTO [UnderWritin].[dbo].[customerrenewal]
           ([reqno]
           ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population]
           ,[expirydate])
   
   
           
SELECT @newrequestid
      ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population])
   
  FROM [UnderWriting].[dbo].[customerinfo]
where reqno = @oldrequestid

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 09:21:14
in the above iam using the belo code to update the reqno in a table everytime a record is inserted

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @oldrequestid
update requesttable
set key_id = key_id + 1
where table_name = 'adhocrequests'
Select @newrequestid = key_id from requesttable where table_name = 'adhocrequests'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 10:15:44
you dont need cursor here. the above code can be simplifies as


DECLARE @newrequestid varchar(50)

update requesttable
set key_id = key_id + 1 
where table_name = 'adhocrequests'

Select @newrequestid = key_id from requesttable where table_name = 'adhocrequests'

     
INSERT INTO [UnderWritin].[dbo].[customerrenewal]
           ([reqno]
           ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population]
           ,[expirydate])
   
   
           
SELECT @newrequestid
      ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population])
   
  FROM [UnderWriting].[dbo].[customerinfo]
where reqno = 111111


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 10:27:25
Thank you.how would i use the logic in insert n number of records baased on the diffrence of two datetime feilds
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 10:29:02
difference of which two datetime fields? and what should be exact logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 10:37:18
using the below datediff logic i would need to run the insert logic n number of times to insert n number of records in the customerrenewal table

select datediff(month, orgeffdt,nxtrnwldt) from customerinfo
where requetno in (111111)


so say the datediff between the two feilds is 10 i should be able to insert 10 records in the customerrenewal table using the below logic


DECLARE @newrequestid varchar(50)

update requesttable
set key_id = key_id + 1 
where table_name = 'adhocrequests'

Select @newrequestid = key_id from requesttable where table_name = 'adhocrequests'

     
INSERT INTO [UnderWritin].[dbo].[customerrenewal]
           ([reqno]
           ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population]
           ,[expirydate])
   
   
           
SELECT @newrequestid
      ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population])
   
  FROM [UnderWriting].[dbo].[customerinfo]
where reqno = 111111








quote:
Originally posted by visakh16

difference of which two datetime fields? and what should be exact logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 10:39:19
ok thats fine. But you'll end up putting same value for reqno field. is that what desire? whats the PK of the table customer renewal?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 10:43:28
nope.we should always increment the requestid feild in the requesttable
and use the newrequestid for inserting a new record

reqno is the primarykey in customerrenewal table
quote:
Originally posted by visakh16

ok thats fine. But you'll end up putting same value for reqno field. is that what desire? whats the PK of the table customer renewal?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 10:50:30
then your current logic wont work. you're just incrementing it in table once (i didnt understand why you're changing it in table in first place) and then using it after

in my opinion. you should get maxid from table then keep on incrementing based on record count to generate next sequnetial values

ie something like


DECLARE @newrequestid varchar(50)


Select @newrequestid = max(key_id) from requesttable where table_name = 'adhocrequests'

     
INSERT INTO [UnderWritin].[dbo].[customerrenewal]
           ([reqno]
           ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population]
           ,[expirydate])
   
   
           
SELECT @newrequestid + (v.number-1)
      ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population])
   
  FROM [UnderWriting].[dbo].[customerinfo] c
CROSS JOIN master..spt_values v
where reqno = 111111
and v.number BETWEEN 1 AND datediff(month, orgeffdt,nxtrnwldt) 
and v.type='p'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 10:55:19
I was never sure that my initial cursor logic would work for the repetetive insertions based on the count from the datediff
I will test the logic that you have given and let you know

Thank you for this
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 11:12:05
ok...will wait for you to come back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 12:41:40
The query executed scuccessfully but it did not insert records into the customerrenewal table and also did not increment the adhocrequests in the requesttable

What might be going wrong?
quote:
Originally posted by visakh16

then your current logic wont work. you're just incrementing it in table once (i didnt understand why you're changing it in table in first place) and then using it after

in my opinion. you should get maxid from table then keep on incrementing based on record count to generate next sequnetial values

ie something like


DECLARE @newrequestid varchar(50)


Select @newrequestid = max(key_id) from requesttable where table_name = 'adhocrequests'

     
INSERT INTO [UnderWritin].[dbo].[customerrenewal]
           ([reqno]
           ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population]
           ,[expirydate])
   
   
           
SELECT @newrequestid + (v.number-1)
      ,[receiveddate]
           ,[neededby]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportfrequency]
           ,[sbotext]
           ,[includeprior]
           ,[clmsrvdt]
           ,[clmsrvdtfrom]
           ,[clmsrvdtthru]
           ,[clmpddt]
           ,[clmpddtfrom]
           ,[clmpddtthru]
           ,[spclhnlg]
           ,[orgeffdt]
           ,[nxtrnwldt]
           ,[lstrnwldt]
           ,[enmem]
           ,[ReqSummary]
           ,[EligFromDate]
           ,[EligThruDate]
           ,[InclPHI]
           ,[BeneTypeMed]
           ,[BeneTypeMH]
           ,[BeneTypeRx]
           ,[BeneTypeSA]
           ,[BeneTypeOth]
           ,[BeneEffDate]
           ,[Population])
   
  FROM [UnderWriting].[dbo].[customerinfo] c
CROSS JOIN master..spt_values v
where reqno = 111111
and v.number BETWEEN 1 AND datediff(month, orgeffdt,nxtrnwldt) 
and v.type='p'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 13:07:11
see if you've required records in [UnderWriting].[dbo].[customerinfo] table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 13:33:27
the requestid which i used to test exists in the customerinfo table.1 record is inserted into the customerrenewal table
but 2 issues are noted
1.It did not increment the adhocrequests in the requesttable(this is important step of the process because the next records requestid is fetched from this table)
2.because it did not increment the requesttable may be it is not able to insert another record as it is creating duplicates
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-13 : 13:35:10
1. if this is a part of your requirement include earlier update
2. try it after including update too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2012-09-13 : 13:45:21
i am not sure what you meant by earlier update
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site