• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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  
0
NitinLothumalla
Asked:
NitinLothumalla
  • 6
  • 4
  • 2
1 Solution
 
copyPasteGhostCommented:
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
0
 
copyPasteGhostCommented:
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
0
 
BrandonGalderisiCommented:
copypaste... that's not SQL Server Syntax.

Nitin... do you have an identity column in the source table?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
copyPasteGhostCommented:
right the syntax might be wrong but the logic is sound.
 :) I had mentioned that.
0
 
BrandonGalderisiCommented:
Actually it isn't.  There is no logic in the statement to prevent it from inserting the same records over and over again.
0
 
copyPasteGhostCommented:
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

0
 
BrandonGalderisiCommented:
SQL Server doesn't have SKIP.  This is the basic technique I was getting at in asking about the identity column.

Working from copyPaste's post...



declare @iCount int
,@batchsize int
,@batches int
,@maxIdent numeric(11,1)

set @batchsize=100
select @maxIdent = max(IdidentityColumnName) from clpr_accounts_v
set @batches = ceiling(@maxdent/@batchSize)

set @iCount = 0
While @iCount <= @batches
begin

 INSERT INTO REVENUE_COMMON  (list_columns_out....)
  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;  
where dbo.clpr_accounts_v.IdidentityColumnName >= (@icount * @batchSize)
  and dbo.clpr_accounts_v.IdidentityColumnName < (@icount * @batchSize)  + @batchSize

Set @iCount = @iCount +1
End
0
 
copyPasteGhostCommented:
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
0
 
BrandonGalderisiCommented:
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.
0
 
copyPasteGhostCommented:
ok thanks.

hopefully the asker will be back soon :)

CPG
0
 
NitinLothumallaAuthor Commented:
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.
0
 
NitinLothumallaAuthor Commented:
Solved the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now