How to Break/Split Bulk Inserts into Small Inserts ?
Posted on 2010-09-13
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
ALTER Procedure [dbo].[spLoad_CLPR_REVENUE_COMMON]
SET NOCOUNT ON
INSERT INTO REVENUE_COMMON
,cast(ACC_Co_NO as integer) as CO_cd
,cast(Cct_NO as integer) as Cc_cd
--,xtrct_dt as report_month
,MONTH_DT as report_month
,'CLPR' as source
, 0 as NON_ANN_REVENUE
,ANN_REV as ANN_REVENUE
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;