近期接到一个需求需要将一个大的单据拆分成多个小单据，这几个小单据主信息要相同，除了单号和ID外其他都要一样，于是

我考虑了借用游标和循环来进行拆分数据，并最终实现了该方法，通过了测试，代码如下：

       IF OBJECT_ID(N'tempdb..#tmpOrgList', 'U') IS NOT NULL  

  drop table #tmpOrgList 

---获取所有有客户的分公司的列表

select distinct OrgID into #tmpOrgList from v_WS400TelSurveyCustDetl 

--声明新建单据的ID和BillNo

declare @intBillID int=0 

declare @chvBillNO nvarchar(20)

---季度首月的第一天

declare @dtLastQtStart datetime 

---季度的年和季度

declare @intYearLastQt int

declare @intLastQt int

---自动执行时赋值当前年度和当前季度

set @dtLastQtStart=DATEADD(qq, DATEDiff(qq,1,GETDATE()), 0)

set @intYearLastQt=Year(@dtLastQtStart)

set @intLastQt=DATEPART(QQ,@dtLastQtStart)

---手动执行时重新赋值指定的年度和季度

if @Flage=1 

begin

set @intYearLastQt=@intYear

set @intLastQt=@intQt

end

---获取所有的抽查明细列表,去除重复的数据

IF OBJECT_ID(N'tempdb..#tmpSVDetlList', 'U') IS NOT NULL  

 drop table #tmpSVDetlList 

select distinct 

[OrgID],[CompanyID],[DistributorID],[CompBranchID],[CustID],[CustChannelID]

,[CustName],isnull([CustContact],'') as Contacts,isnull([MobilePhone],'') as MobilePhone,

isnull([CustTel],'') as ContactPhone,

[CustAddr] as ClientAddress,[AreaID],[AreaType],[AreaName]

,[IsShow],GETDATE() as CreateTime,2 as BillerID,GETDATE() as ModifyTime 

into #tmpSVDetlList

from v_WS400TelSurveyCustDetl 

declare @intOrgSVDetlCount int

declare @intOrgSVDetlPage int 

declare @intPageCount int

declare @intPage int=0 

---每个分公司明细超过500条就开始拆分

set @intPageCount=500

DECLARE WS400_Cursor Cursor Local For

SELECT OrgID FROM #tmpOrgList

OPEN WS400_Cursor

DECLARE @intOrgID int

FETCH NEXT FROM WS400_Cursor INTO @intOrgID

WHILE @@FETCH_STATUS=0 

BEGIN

BEGIN TRY

---判断所属分公司数据量是否超过了500条,超过了则拆分成几条数据来插入

---先生成单个分公司的抽查数据并插入临时表中

IF OBJECT_ID(N'tempdb..#tmpOrgSVDetlList', 'U') IS NOT NULL  

drop table #tmpOrgSVDetlList 

 

select 

@intBillID as BillID,ROW_NUMBER() over (order by CompanyID asc,CustID asc) as SNO,

[OrgID],[CompanyID],[DistributorID],[CompBranchID],[CustID],[CustChannelID]

,[CustName],[Contacts],[MobilePhone],[ContactPhone],

[ClientAddress],[AreaID],[AreaType],[AreaName]

,[IsShow],GETDATE() as CreateTime,2 as BillerID,GETDATE() as ModifyTime 

into #tmpOrgSVDetlList 

from #tmpSVDetlList 

where OrgID=@intOrgID 

---获取当前这个分公司的所有抽查数据的数量

select @intOrgSVDetlCount=COUNT(1) from #tmpOrgSVDetlList

set  @intOrgSVDetlPage=@intOrgSVDetlCount/@intPageCount 

set @intPage=0 

---分批循环插入

while @intPage<=@intOrgSVDetlPage 

begin

----如果明细数量刚好是分页整数倍则最后一次拆分不执行

if @intPage*@intPageCount=@intOrgSVDetlCount 

begin

continue

end

---获取ID

exec dbo.prGetIdentityNoTrans 'WS400TelSurvey',@intBillID output 

---获取BillNO

exec dbo.prGetBillNo 40001,-1,-1,-1,@chvBillNO output 

---插入主表

insert into WS400TelSurvey

(

BillID,

BillNO,

OrgID,

SVYear,

SVquarter,

CreateTime,

BillerID,

ModifyTime,

Remark

)

select @intBillID,@chvBillNO,@intOrgID,@intYearLastQt,@intLastQt,GETDATE(),2,GETDATE(),''

--插入明细表

insert into WS400TelSurveyDetl(

BillID,

SNO,

OrgID,

CompanyID,

DistributorID,

CompBranchID,

CustID,

CustChannelID,

CustName,

Contacts,

MobilePhone,

ContactPhone,

ClientAddress,

AreaID,

AreaType,

AreaName,

IsShow,

CreateTime,

BillerID,

ModifyTime

)

select 

@intBillID as [BillID],[SNO],

[OrgID],[CompanyID],[DistributorID],[CompBranchID],[CustID],[CustChannelID]

,[CustName],[Contacts],[MobilePhone],[ContactPhone],

[ClientAddress],[AreaID],[AreaType],[AreaName]

,[IsShow],GETDATE() as CreateTime,2 as BillerID,GETDATE() as ModifyTime

from #tmpOrgSVDetlList  

where OrgID=@intOrgID and SNO<=(@intPage+1)*@intPageCount 

and SNO>@intPageCount*@intPage

set @intPage=@intPage+1

end

IF OBJECT_ID(N'tempdb..#tmpOrgSVDetlList', 'U') IS NOT NULL  

drop table #tmpOrgSVDetlList 

END TRY

BEGIN CATCH

print 'error'

END CATCH

FETCH NEXT FROM WS400_Cursor INTO @intOrgID 

END

CLOSE WS400_Cursor 

DEALLOCATE WS400_Cursor 

IF OBJECT_ID(N'tempdb..#tmpOrgList', 'U') IS NOT NULL  

drop table #tmpOrgList 

 

IF OBJECT_ID(N'tempdb..#tmpSVDetlList', 'U') IS NOT NULL  

drop table #tmpSVDetlList