批量生成記錄的一種解決方法

---------------------------------------Create by RogerWang 2006-03-05----------------------------
/*
已知：有A,B,C三表，其中A是主表,B,C是其從表,且C表中Key是自增性.
ABC三表中現在都有少量記錄，其中A表的記錄數是20條，其Key值連續，且Key的規則是呈流水型增長

要求：由已知的20條記錄，自動生成10W條或50W條記錄

*/

/*批量插入數據導入的基本思路：
      利用臨時表和Select 語句實現批量插入,避免將每個表的所有字段列出來,相對方便和靈活.
      對於不存在自增字段的表(主表),按照流水號的生成規則生成新的關鍵字
      對於存在自增字段的表(從表),與主表對應的外鍵處理方法與主表一樣,而自增的關鍵字段,則在插入時不對其賦值.
*/
/*
   成功執行條件是：初始給的記錄的Key值是連續的
*/
/*
注意：
       當插入記錄總數改變時，要改動的地方有@AddRecord的初始值

       噹初始已完成記錄數或初始未完成記錄數改變時,
               首先請改動@FinishRecord或@UnFinishRecord的值
               然後請改動取出最初初始已完成記錄數的SQL語句中的TOP N 中的N值。

       噹插入完成記錄數與未完成記錄數操作互換時，要改動的地方有：
             1: 循環次數
             2:取最初記錄的SQL
             3：copy workflow data 功能中的循環次數
*/
set nocount on

-----------定義事務處理辦法
set xact_abort on
------------------定義要插入表中的記錄,默認是100000條,到正式執行腳本時,可以作為參數傳入
declare @AddRecord Int----總記錄數
declare @recleNum int --- 循環次數
declare @FinishRecord int ------- 初始已完成記錄數
declare @UnFinishRecord int -----初始未完成記錄數
declare @InitRecord int -------初始的所有記錄數=(已完成記錄+未完成記錄)

---賦總記錄數的值
set @AddRecord = 90000

set @FinishRecord = 10
set @UnFinishRecord = 10
set @InitRecord = @FinishRecord + @UnFinishRecord
/*
但執行已完成記錄循環次數=(要插入記錄數)/初始已完成記錄數
其中:要插入的記錄數=(總記錄數-初始已完成記錄數)
*/
set @recleNum = (@AddRecord - @FinishRecord)/@FinishRecord ----------插入已完成記錄數的情況

---set @recleNum = (@AddRecord - @UnFinishRecord)/@UnFinishRecord ----------插入已完成記錄數的情況

--------定義步長，為在循環中改變的數字
declare @iStep int
set @iStep = 1

begin tran
/*
   插入出口成品相關記錄表
*/
/*
-------將moem_cExportID修改為從1000000開始,以免出現與實際操作時出現的數據沖突的問題
Update MOExportMaster_moem Set moem_cExportID = substring(moem_cExportID,1,1)+cast((substring(moem_cExportID,2,7)+1000000) as varchar(7))+substring(moem_cExportID,9,Len(moem_cExportID))
Update MOExpMaterialList_meml Set meml_cExportID = substring(meml_cExportID,1,1)+cast((substring(meml_cExportID,2,7)+1000000) as varchar(7)) +substring(meml_cExportID,9,Len(meml_cExportID))
UPdate MOExpProductList_mepl set Mepl_cExportID = substring(Mepl_cExportID,1,1)+cast((substring(Mepl_cExportID,2,7)+1000000) as varchar(7))+substring(Mepl_cExportID,9,Len(Mepl_cExportID))
*/

--------------判斷臨時表是否存在,如果存在,清空!
if object_id('tempdb.dbo.#tmpMoem') is not null
   drop table #tmpMoem
if object_id('tempdb.dbo.#tmp_Moem') is not null
   drop table #tmp_Moem

if object_id('tempdb.dbo.#tmpmeml') is not null
drop table #tmpmeml
if object_id('tempdb.dbo.#tmp_meml') is not null
drop table #tmp_meml

if object_id('tempdb.dbo.#tmpMepl') is not null
drop table #tmpMepl
if object_id('tempdb.dbo.#tmp_Mepl') is not null
drop table #tmp_Mepl

/*
----建立兩個有關tbOpma的臨時表
select Top 10 * into #tmpMoem from MOExportMaster_moem order by moem_cExportID
select Top 10 * into #tmp_Moem from MOExportMaster_moem order by moem_cExportID

----建立兩個有關Obpd的臨時表
select * into #tmpmeml from MOExpMaterialList_meml where meml_cExportID in (select moem_cExportID from #tmpMoem)
select * into #tmp_meml from MOExpMaterialList_meml where meml_cExportID in (select moem_cExportID from #tmpMoem)

----建立兩個有關Obpd的臨時表
select * into #tmpMepl from MOExpProductList_mepl where Mepl_cExportID in (select moem_cExportID from #tmpMoem)
select * into #tmp_Mepl from MOExpProductList_mepl where Mepl_cExportID in (select moem_cExportID from #tmpMoem)
*/

----建立兩個有關tbOpma的臨時表,指定moem_cExportID的范圍
select Top 10 * into #tmpMoem from MOExportMaster_moem where (moem_cExportID>='S1600001-A' and moem_cExportID<= 'S1600010-A ' ) order by moem_cExportID
select Top 10 * into #tmp_Moem from MOExportMaster_moem where (moem_cExportID>='S1600001-A' and moem_cExportID<= 'S1600010-A ' ) order by moem_cExportID

----將臨時表中的自增字段刪除
Exec ('Alter Table #tmpmeml Drop Column meml_iExportNo ')
Exec ('Alter Table #tmp_meml Drop Column meml_iExportNo ')

----dbcc checkident(OrderBoxProduct_obpd,reseed,10) ---處理自增型字段最初值的問題

--循環插入記錄

while @iStep <= @recleNum
begin

--
update #tmpMoem set moem_cExportID= substring(moem_cExportID,1,1)+cast((substring(moem_cExportID,2,7)+(@iStep*@InitRecord)) as varchar(7))+substring(moem_cExportID,9,Len(moem_cExportID))
update #tmpmeml set meml_cExportID= substring(meml_cExportID,1,1)+cast((substring(meml_cExportID,2,7)+(@iStep*@InitRecord)) as varchar(7)) +substring(meml_cExportID,9,Len(meml_cExportID))
update #tmpMepl set Mepl_cExportID = substring(Mepl_cExportID,1,1)+cast((substring(Mepl_cExportID,2,7)+(@iStep*@InitRecord)) as varchar(7))+substring(Mepl_cExportID,9,Len(Mepl_cExportID))

---插入MOExportMaster_moem表
   insert MOExportMaster_moem
   select   *
   from #tmpMoem
--插入MOExpMaterialList_meml表
   insert into MOExpMaterialList_meml
   select *
   from #tmpmeml

   insert into MOExpProductList_mepl
   select *
   from #tmpMepl

--清空#tmpMoem記錄
delete from #tmpMoem
delete from #tmpmeml
delete from #tmpMepl

   insert #tmpMoem
   select *
   from #tmp_Moem

   insert #tmpmeml
   select *
   from #tmp_meml

   insert #tmpMepl
   select *
   from #tmp_Mepl

set @iStep =@iStep +1
end

------執行事務
if @@error<>0
begin
rollback tran
end
else
commit tran

-------------------------------清空臨時表
---------卡板相關臨時表

drop table #tmpMoem
drop table #tmp_Moem

drop table #tmpmeml
drop table #tmp_meml

drop table #tmpMepl
drop table #tmp_Mepl

set xact_abort off
set nocount off

发表于 2006-03-10 10:15 RogerWang 阅读(960) 评论(0) 收藏举报

刷新页面返回顶部

批量生成記錄的一種解決方法

公告