Inserting multiple records - SQL Server Forums

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

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-31 : 10:37:54

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

52326 Posts

Posted - 2012-10-31 : 12:13:35

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources