Link to home
Start Free TrialLog in
Avatar of NitinLothumalla
NitinLothumallaFlag for United States of America

asked on

How to Break/Split Bulk Inserts into Small Inserts ?

SQL Server 2005; Simple Recovery Model
The Transaction Log is set to clear every 4 minutes.

We have few SP's that do a Bulk Insertions. As the Bulk Inserts are too many, DB gets full and transaction fails. How can I achieve breaking the Bulk Insertions into smaller inserts ?

If I get a solution in one SP, then I can implement it in other SP's...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spLoad_CLPR_REVENUE_COMMON]  
   
AS  
 
SET NOCOUNT ON        
BEGIN    
 
  INSERT INTO REVENUE_COMMON  
  SELECT [UNICA_REFERRAL_ID]  
     ,cast(ACC_Co_NO as integer) as CO_cd  
     ,cast(Cct_NO as integer) as Cc_cd  
     ,gl.SALE_CHN_CD  
     --,xtrct_dt as report_month  
     ,MONTH_DT as report_month  
     ,[rel_month]  
     ,[acct_referral_ind]  
     ,[rev_ind]  
     ,[ACCNO]  
     ,'CLPR' as source  
     , product  
     , 0 as NON_ANN_REVENUE  
     ,ANN_REV  as ANN_REVENUE  
     ,balance  
         
  FROM dbo.CLPR_Accounts_v  
  LEFT JOIN dbo.GL_ORG_HIERARCHY_RANGES gl  
  ON month_dt between gl.FromDate and gl.todate  
  AND CAST(ACC_Co_NO as integer) = gl.co_no  
  AND CAST(Cct_NO as integer)= gl.cc_no  
  --WHERE rel_month BETWEEN -13 AND 13;  
 
END  
Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image

declare $iCount int;

While $iCount < 100
 INSERT INTO REVENUE_COMMON  
  SELECT top 100  [UNICA_REFERRAL_ID]  
     ,cast(ACC_Co_NO as integer) as CO_cd  
     ,cast(Cct_NO as integer) as Cc_cd  
     ,gl.SALE_CHN_CD  
     --,xtrct_dt as report_month  
     ,MONTH_DT as report_month  
     ,[rel_month]  
     ,[acct_referral_ind]  
     ,[rev_ind]  
     ,[ACCNO]  
     ,'CLPR' as source  
     , product  
     , 0 as NON_ANN_REVENUE  
     ,ANN_REV  as ANN_REVENUE  
     ,balance  
         
  FROM dbo.CLPR_Accounts_v  
  LEFT JOIN dbo.GL_ORG_HIERARCHY_RANGES gl  
  ON month_dt between gl.FromDate and gl.todate  
  AND CAST(ACC_Co_NO as integer) = gl.co_no  
  AND CAST(Cct_NO as integer)= gl.cc_no  
  --WHERE rel_month BETWEEN -13 AND 13;  


Set $iCount = $iCount +1;
End While



Something like that. the syntax might be wrong but the logic is good. :)

CPG
this will enter 100 * 100 records.

if you want to have a break inbetween record entrys you can use the WaitFor command..


-- wait for 1 minute
WAITFOR DELAY '00:01'
 
-- wait for 1 second
WAITFOR DELAY '00:00:01'


Just put that  before the:

Set $iCount = $iCount +1;

Hope that helps.
CPG
copypaste... that's not SQL Server Syntax.

Nitin... do you have an identity column in the source table?
right the syntax might be wrong but the logic is sound.
 :) I had mentioned that.
Actually it isn't.  There is no logic in the statement to prevent it from inserting the same records over and over again.
Savvy Indeed...

How about this?


declare $iCount int;

While $iCount < 100
 INSERT INTO REVENUE_COMMON  
  SELECT top 100 skip ($iCount *100) [UNICA_REFERRAL_ID]  
     ,cast(ACC_Co_NO as integer) as CO_cd  
     ,cast(Cct_NO as integer) as Cc_cd  
     ,gl.SALE_CHN_CD  
     --,xtrct_dt as report_month  
     ,MONTH_DT as report_month  
     ,[rel_month]  
     ,[acct_referral_ind]  
     ,[rev_ind]  
     ,[ACCNO]  
     ,'CLPR' as source  
     , product  
     , 0 as NON_ANN_REVENUE  
     ,ANN_REV  as ANN_REVENUE  
     ,balance  
         
  FROM dbo.CLPR_Accounts_v  
  LEFT JOIN dbo.GL_ORG_HIERARCHY_RANGES gl  
  ON month_dt between gl.FromDate and gl.todate  
  AND CAST(ACC_Co_NO as integer) = gl.co_no  
  AND CAST(Cct_NO as integer)= gl.cc_no  
  --WHERE rel_month BETWEEN -13 AND 13;  


Set $iCount = $iCount +1;
End While

ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
can't we also do something like this?

http://www.sqlservercurry.com/2009/06/skip-and-take-n-number-of-records-in.html

therefore knowing the identity would not be necessary?

CPG
You could... but the problem is that with each iteration of the loop, row_number() will be calculated for all records in the result set.  That would make it inefficient.
ok thanks.

hopefully the asker will be back soon :)

CPG
Avatar of NitinLothumalla

ASKER

Hello All,  

Thank you very much for all your responses. I am still waiting for a reply from my colleagues as I do not have access to the box.... I was just asked to look at the logic.

I will update soon and let you know if the solution stands good..... You guys are amazing, thank you. I am not sure about the solution but the responses from your end was pretty quick :)

Nitin.
Solved the problem