Solved

How to Break/Split Bulk Inserts into Small Inserts ?

Posted on 2010-09-13
12
274 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
  • 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now