Copying a request from draft tables to original ta

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

Copying a request from draft tables to original ta

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-06 : 20:05:15

Hi All
i have been using the below query to copy a record of data that exists in several draft tables to the original tables. how do i change this query such that i can input multiple records at one time so multiple records get copied to original tables from the draft tables

At this time when i feed mutiple requestids to my query it simply errors out

SET NOCOUNT ON
GO 
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)

DECLARE db_cursor CURSOR FOR 
SELECT RequestId from report_request_draft where requestid in (320762)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @oldrequestid
Select @newrequestid = @oldrequestid


WHILE @@FETCH_STATUS = 0   
BEGIN  
     
     --do work here    
    
     INSERT INTO REPORT_REQUEST([requestid]
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           )
select @newrequestid
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           
FROM [UnderWriting].[dbo].[REPORT_REQUEST_DRAFT]
  where requestid = @oldrequestid

  



INSERT INTO [UnderWriting].[dbo].[REQUEST_POLICIES]
           ([RequestId]
           ,[CSN]
           ,[PolicyName]
           ,[LOB]
           ,[Platform])
           
     SELECT @newrequestid
      ,[CSN]
      ,[PolicyName]
      ,[LOB]
      ,[Platform]
  FROM [UnderWriting].[dbo].[REQUEST_POLICIES_DRAFT]
  where requestid = @oldrequestid

INSERT INTO [UnderWriting].[dbo].[SELECTED_REPORT]
           ([RequestId]
           ,[rptdesc]
           ,[rptseqno]
           ,[pltfmcd]
           ,[rpt_cmpt_ind]
           ,[assignto])
           SELECT @newrequestid
      ,[rptdesc]
      ,[rptseqno]
      ,[pltfmcd]
      ,'O'
      ,(assignto) as assignto
FROM [UnderWriting].[dbo].[SELECTED_REPORT_DRAFT]
  where requestid = @oldrequestid

  
  INSERT INTO [UnderWriting].[dbo].[REPORT_DISTRIBUTION]
           ([RequestId]
           ,[rptdis]
           ,[Seq]
           ,[recipnm]
           ,[conm]
           ,[stadr1]
           ,[stadr2]
           ,[ctystzip]
           ,[emailadr]
           ,[Media_cd]
           ,[Format_cd]
           ,[phone]
           ,[OtherText])
     SELECT 
     @newrequestid
      ,[rptdis]
      ,[Seq]
      ,[recipnm]
      ,[conm]
      ,[stadr1]
      ,[stadr2]
      ,[ctystzip]
      ,[emailadr]
      ,[Media_cd]
      ,[Format_cd]
      ,[phone]
      ,[OtherText]
  FROM [UnderWriting].[dbo].[REPORT_DISTRIBUTION_DRAFT]
   where requestid = @oldrequestid 




FETCH NEXT FROM db_cursor INTO @oldrequestid

END  
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 00:56:46

you dont need any cursor for this. just use a logic like


INSERT INTO REPORT_REQUEST([requestid]
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           )
select      [requestid]
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           
FROM [UnderWriting].[dbo].[REPORT_REQUEST_DRAFT] t
WHERE EXISTS (SELECT 1 from report_request_draft where requestid in (320762,...) AND requestid  = t.requestid)


  



INSERT INTO [UnderWriting].[dbo].[REQUEST_POLICIES]
           ([RequestId]
           ,[CSN]
           ,[PolicyName]
           ,[LOB]
           ,[Platform])
           
     SELECT [requestid]
      ,[CSN]
      ,[PolicyName]
      ,[LOB]
      ,[Platform]
  FROM [UnderWriting].[dbo].[REQUEST_POLICIES_DRAFT] t
  WHERE EXISTS (SELECT 1 from report_request_draft where requestid in (320762,...) AND requestid  = t.requestid)


INSERT INTO [UnderWriting].[dbo].[SELECTED_REPORT]
           ([RequestId]
           ,[rptdesc]
           ,[rptseqno]
           ,[pltfmcd]
           ,[rpt_cmpt_ind]
           ,[assignto])
           SELECT [requestid]
      ,[rptdesc]
      ,[rptseqno]
      ,[pltfmcd]
      ,'O'
      ,(assignto) as assignto
FROM [UnderWriting].[dbo].[SELECTED_REPORT_DRAFT] t
WHERE EXISTS (SELECT 1 from report_request_draft where requestid in (320762,...) AND requestid  = t.requestid)

  
  INSERT INTO [UnderWriting].[dbo].[REPORT_DISTRIBUTION]
           ([RequestId]
           ,[rptdis]
           ,[Seq]
           ,[recipnm]
           ,[conm]
           ,[stadr1]
           ,[stadr2]
           ,[ctystzip]
           ,[emailadr]
           ,[Media_cd]
           ,[Format_cd]
           ,[phone]
           ,[OtherText])
     SELECT 
     [requestid]
      ,[rptdis]
      ,[Seq]
      ,[recipnm]
      ,[conm]
      ,[stadr1]
      ,[stadr2]
      ,[ctystzip]
      ,[emailadr]
      ,[Media_cd]
      ,[Format_cd]
      ,[phone]
      ,[OtherText]
  FROM [UnderWriting].[dbo].[REPORT_DISTRIBUTION_DRAFT] t
  WHERE EXISTS (SELECT 1 from report_request_draft where requestid in (320762,...) AND requestid  = t.requestid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-08 : 10:23:23

Thanks Visakh . Is there a way where i can feed all the requests at one place and the SQL can use it from there instead of me feeding requestids in all queries .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 04:35:37

yep..create a table variable populating the passed values at first. then use it in joinin other places.
ie like


SELECT Val INTO #Temp
FROM dbo.ParseValues(@variable,',')

INSERT INTO REPORT_REQUEST([requestid]
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           )
select      [requestid]
           ,[receiveddate]
           ,[neededby]
           ,[billingerror]
           ,[billingerrtext]
           ,[busneed]
           ,[rptcomments]
           ,[reportcontent]
           ,[reportlayout]
           ,[sorts]
           ,[reportfrequency]
           ,[defineschedule]
           ,[schedulebasedon]
           
FROM [UnderWriting].[dbo].[REPORT_REQUEST_DRAFT] t
WHERE EXISTS (SELECT 1 from report_request_draft r inner join #Temp t on r.requestid = t.val AND requestid  = t.requestid)


  



INSERT INTO [UnderWriting].[dbo].[REQUEST_POLICIES]
           ([RequestId]
           ,[CSN]
           ,[PolicyName]
           ,[LOB]
           ,[Platform])
           
     SELECT [requestid]
      ,[CSN]
      ,[PolicyName]
      ,[LOB]
      ,[Platform]
  FROM [UnderWriting].[dbo].[REQUEST_POLICIES_DRAFT] t
  WHERE EXISTS (SELECT 1 from report_request_draft r inner join #Temp t on r.requestid = t.val AND requestid  = t.requestid)


INSERT INTO [UnderWriting].[dbo].[SELECTED_REPORT]
           ([RequestId]
           ,[rptdesc]
           ,[rptseqno]
           ,[pltfmcd]
           ,[rpt_cmpt_ind]
           ,[assignto])
           SELECT [requestid]
      ,[rptdesc]
      ,[rptseqno]
      ,[pltfmcd]
      ,'O'
      ,(assignto) as assignto
FROM [UnderWriting].[dbo].[SELECTED_REPORT_DRAFT] t
WHERE EXISTS (SELECT 1 from report_request_draft r inner join #Temp t on r.requestid = t.val AND requestid  = t.requestid)

  
  INSERT INTO [UnderWriting].[dbo].[REPORT_DISTRIBUTION]
           ([RequestId]
           ,[rptdis]
           ,[Seq]
           ,[recipnm]
           ,[conm]
           ,[stadr1]
           ,[stadr2]
           ,[ctystzip]
           ,[emailadr]
           ,[Media_cd]
           ,[Format_cd]
           ,[phone]
           ,[OtherText])
     SELECT 
     [requestid]
      ,[rptdis]
      ,[Seq]
      ,[recipnm]
      ,[conm]
      ,[stadr1]
      ,[stadr2]
      ,[ctystzip]
      ,[emailadr]
      ,[Media_cd]
      ,[Format_cd]
      ,[phone]
      ,[OtherText]
  FROM [UnderWriting].[dbo].[REPORT_DISTRIBUTION_DRAFT] t
  WHERE EXISTS (SELECT 1 from report_request_draft r inner join #Temp t on r.requestid = t.val AND requestid  = t.requestid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 04:53:47

ParseValues UDF can be found here
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-01-10 : 08:38:13

Thanks again. very helpful

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 09:40:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources