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
 Inserting multiple records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/31/2012 :  10:37:54  Show Profile  Reply with Quote
Hi All
I have a working SQL that i have been using to insert records in a table(Renewals) based on count derived from subtracting two date feilds in another table(customerinfo)

before inserting each record in renewals it increments the requestid counter that we store in table_keys table

The below SQL works perfect when a request has only 1 report in it

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 customerinfo where requestid in (149016)

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 customerinfo 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].[renewals]
([roc_id]
,[rptdesc]
,[rptrsrcetxt]
,[tat]
,[HRS]
,[mins]
,[busneed]
,[origrequestid]
)

SELECT @newrequestid
,SR.rptdesc
,SR.resource
,SR.tat
,SR.hrs
,SR.mins
,RR.busneed
,@oldrequestid



FROM [dbo].[CustomerInfo] RR INNER JOIN SELECTED_Customer 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


My problem is how do i change this SQL so that it checks how many reports are there for each request in the rptdesc feild in SELECTED_Customer table and insert @num_of_times+1 records for each request


An example
If a request has 2 report types and the calcualted feild is 6
then i need to inset 7 records for each report in the renewals table

which means total of 14

Thanks


visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2012 :  12:13:35  Show Profile  Reply with Quote
i think you need just this


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

;With CTE
AS
(
SELECT @newrequestid +1 AS roc_id
,SR.rptdesc
,SR.resource
,SR.tat
,SR.hrs
,SR.mins
,RR.busneed
,RR.requestid
,datediff(month, receiveddate,expirationdate)+1 AS DiffVal
,1 AS Counter 
FROM [dbo].[CustomerInfo] RR 
INNER JOIN SELECTED_Customer SR
ON RR.requestid = SR.requestid
where RR.requestid = 149016

UNION ALL

SELECT roc_id+1
,rptdesc
,resource
,tat
,hrs
,mins
,busneed
,requestid
,DiffVal
,Counter  + 1
FROM CTE
WHERE Counter + 1 <=DiffVal
)

INSERT INTO [UnderWriting].[dbo].[renewals]
([roc_id]
,[rptdesc]
,[rptrsrcetxt]
,[tat] 
,[HRS]
,[mins]
,[busneed]
,[origrequestid]
)
SELECT roc_id
,rptdesc
,resource
,tat
,hrs
,mins
,busneed
,requestid
FROM CTE


update table_keys
set key_id = (SELECT MAX(roc_id) 
FROM [UnderWriting].[dbo].[renewals]
WHERE [origrequestid]=149016)
where table_name = 'adhoc'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.28 seconds. Powered By: Snitz Forums 2000