Solved

How to Break/Split Bulk Inserts into Small Inserts ?

Posted on 2010-09-13
12
316 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 68
Getting max record but maybe not use Group BY 2 38
Email Notifications for SQL 2005 9 43
What is this datetime? 1 33
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: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…

752 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