Solved

How to Break/Split Bulk Inserts into Small Inserts ?

Posted on 2010-09-13
12
309 Views
Last Modified: 2012-05-10
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
Comment
Question by:NitinLothumalla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33664846
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33664863
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33664874
copypaste... that's not SQL Server Syntax.

Nitin... do you have an identity column in the source table?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33664887
right the syntax might be wrong but the logic is sound.
 :) I had mentioned that.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33664960
Actually it isn't.  There is no logic in the statement to prevent it from inserting the same records over and over again.
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33664985
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33665055
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33665084
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33665112
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
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 33665124
ok thanks.

hopefully the asker will be back soon :)

CPG
0
 

Author Comment

by:NitinLothumalla
ID: 33665197
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
 

Author Closing Comment

by:NitinLothumalla
ID: 33681510
Solved the problem
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question