配送中心数据迁移项目（SDC to PDC Data Migartion）
2010-08-27 10:57 知行思新 阅读(2867) 评论(2) 编辑 收藏 举报最近一直在忙公司里的SDC to PDC Data Migartion项目。原来服务亚太区业务的新加坡物流配送中心要迁移到马来西亚槟城。信息系统中的不少数据需要配合这次迁移做相应的修改，涉及销售订单（Sales Order）、采购订单（Purchase Order）和库存（Inventory）。在与商务分析员的讨论后，我们的方案中有很大一部分数据修改需要在数据库端写脚本完成。前段时间一直在讨论方案、设计逻辑、撰写脚本，到现在脚本基本完成了。在这个过程中，零零星星有些收获，在这里整理一下，作为备忘。
业务需求
新加坡物流配送中心（SDC）为Asean，Shanghai，Taiwan，Korea分公司提供仓储管理与物流配送服务。现在新加坡物流配送中心（SDC）要迁移至槟城（PDC），如下图所示：
数据迁移需要在SDC到PDC切换的时间点一次性完成。
所要做的数据修改按顺序如下：
Section One: Back Order and Un-reserve
1.各分公司把从SDC发货且已经产生ship但未shipconfirm（即还未生成Inventory）的订单做Back Order
2.各分公司把从SDC发货且已经reserve到货的订单做unreserve
Section Two: Open SO Migration
3.各分公司把从SDC发货的订单转到从PDC发货
- 对于从SDC发货的完全Open的订单（即订单中的每一条SOItem都未发货或关闭），直接修改订单的SubInventory信息，使用PDC相应的SubInventroy
- 对于从SDC发货的部分Open的订单（即订单中有些SOItem已经发货或关闭），生成新的从PDC发货的订单（包含那些未发货或关闭的SOItem），Cancel之前SDC的SOItem
Section Three: Open Inventory Migration
4.对于在SDC中的所有当前库存（Inventory）生成销售订单（SalesOrder），发货方为PDC
5.在PDC端创建采购订单（PurchaseOrder），把SDC发出的货物收回PDC仓库
Section Four: Open PO Migration
6.关闭所有SDC的采购订单
开发所得
整个代码非常冗长，贴在这里意义不大。对于其中几个有意思的点，在这里分析一下。
1.分布式数据修改
这次的数据Migration牵扯多个分公司的数据库，而且其中有些表有同步关系（即要保持一致）。
所以经常会有如下场景：在修改某数据库中某表的数据，要把这些修改分发到处于异地的其他数据库中的同步表中。举例来说：对Asean上Table_SalesOrder的数据修改需要分发给Asean、Shanghai、Taiwan、Korea上的同步表Table_SDCSalesOrder。
解决方案：
- 最原始的方式是在SQL Server Management Studio中连接多个数据库，在其中一个数据库上进行数据修改之后，把结果集手工拷贝到其他数据库，再同步修改其他各库中的对应表。当需要同步的结果集很多时，这种方法相当繁琐。反复在不同数据库间切换并进行手工拷贝和同步修改容易出现错误。所以这个方案并不合适。
- 在SQL Server中可以创建linked server进行异地SQL Server的数据查询、修改。创建linked sever如下例所示：
if not exists (select * from sys.servers srv where srv.name = N'sh-mis-xxin') begin EXEC sp_addlinkedserver @server=N'sh-mis-xxin', @srvproduct=N'SQL Server'; EXEC sp_addlinkedsrvlogin @rmtsrvname=N'sh-mis-xxin', @useself=N'False', @locallogin=NULL, @rmtuser=N'UserName', @rmtpassword='Password'; end用linked server是否能解决我们的问题呢？通过实验发现，当需要修改的远程表数据较多，或需要与远程大表进行连接时，linked server性能非常差。所以这个方案也未使用。
- 使用SQL Server Integration Service（SSIS）是否能解决这个问题呢？我觉得肯定能，而且整个Data Migration都可以用SSIS工具完成。但SSIS用得不是很多，而且这次Data Migration的时间又非常紧，所以最终没有采用。我自己计划在项目后，用SSIS把这个Migration重新做一下。
- 第四种方案是在Migration之前，把各个分公司的数据库进行备份压缩，拉到一台主机上并restore到一个SQL Server的instance中。在一个instance上不同数据库的访问是非常方便的，在此instance上进行Data Migration的所有操作。之后再把这些数据库还原回各个分公司。
我们最后选定了第四种方案，原因是各分公司的数据库并不大，而且我们每天都有计划性的数据备份压缩并拉回的维护操作。所以这种方式肯定是可行的。
2.Open SO Migration部分代码分析
Open SO Migration中有一步就是要生成新的销售订单（SalesOrder）和订单明细（SOItem），我们直接看代码：
--2.3.3 Copy the source order to the new order (OE2) --2.3.3.1 Create New SO create table #NewSO ( SO_ID int not null, SO_SourceID int not null ); insert into Table_SalesOrder ( Contact_ID, Customer_ID, Site_ID, SO_OrderDate, SO_OrderStatus, SO_CustomerPO, SO_OrderType, SO_RequestDate, SO_SalesPerson, SO_VAT, EndUserContact_ID, EndUserSite_ID, ShipToContact_ID, ShipToSite_ID, BillToContact_ID, BillToSite_ID, DeliveryToContact_ID, DeliveryToSite_ID, SO_PriceList, SO_CurrencyType, SO_LineSet, SO_PaymentTerm, SO_ShipPriority, SO_PaymentType, SO_ShippingMethod, SO_SalesChannel, SO_FreightTerm, SO_WinProbability, SO_IncoTerm, SO_SourceType, SO_SourceID, SO_WareHouse, SO_ShippingInstructions, SO_PackingInstructions, SO_CreatedBy, SO_CreatedAt, SO_ModifiedBy, SO_ModifiedAt, SO_Activity, SO_InvoicePrintoutType, SO_OrderNotes, SO_TaxStatus, SO_CForm, SO_OUID, SO_CFormReceived, SO_CFormReceivedBy, SO_CFormReceivedAt, SO_CFormNumber, SO_DCNo, SO_PONumber, SO_DCModifiedBy, SO_DCModifiedAt ) output inserted.SO_ID, inserted.SO_SourceID into #NewSO select distinct SO.Contact_ID, SO.Customer_ID, SO.Site_ID, SO.SO_OrderDate, SO.SO_OrderStatus, SO.SO_CustomerPO, SO.SO_OrderType, SO.SO_RequestDate, SO.SO_SalesPerson, SO.SO_VAT, SO.EndUserContact_ID, SO.EndUserSite_ID, SO.ShipToContact_ID, SO.ShipToSite_ID, SO.BillToContact_ID, SO.BillToSite_ID, SO.DeliveryToContact_ID, SO.DeliveryToSite_ID, SO.SO_PriceList, SO.SO_CurrencyType, SO.SO_LineSet, SO.SO_PaymentTerm, SO.SO_ShipPriority, SO.SO_PaymentType, SO.SO_ShippingMethod, SO.SO_SalesChannel, SO.SO_FreightTerm, SO.SO_WinProbability, SO.SO_IncoTerm, N'Order' as SO_SourceType, SO.SO_ID as SO_SourceID, case SO.SO_WareHouse when 1018 then 1041 when 1019 then 1042 when 1026 then 1043 when 1031 then 1044 when 1040 then 1045 end as SO_WareHouse, SO.SO_ShippingInstructions, SO.SO_PackingInstructions, N'SDC to PDC Migration' as SO_CreatedBy, getdate() as SO_CreatedAt, N'SDC to PDC Migration' as SO_ModifiedBy, getdate() as SO_ModifiedAt, SO.SO_Activity, SO.SO_InvoicePrintoutType, SO.SO_OrderNotes, SO.SO_TaxStatus, SO.SO_CForm, SO.SO_OUID, SO.SO_CFormReceived, SO.SO_CFormReceivedBy, SO.SO_CFormReceivedAt, SO.SO_CFormNumber, SO.SO_DCNo, SO.SO_PONumber, SO.SO_DCModifiedBy, SO.SO_DCModifiedAt from #NeedCreateSOItem NeedCreateSOItem inner join Table_SalesOrder SO on NeedCreateSOItem.SO_ID = SO.SO_ID where cast(SO.SO_ID as nvarchar) + cast((case SO.SO_WareHouse when 1018 then 1041 when 1019 then 1042 when 1026 then 1043 when 1031 then 1044 when 1040 then 1045 end) as nvarchar) + N'SDC to PDC Migration' not in (select cast(SO_SourceID as nvarchar) + SO_Warehouse + isnull(SO_CreatedBy, N'') from Table_SalesOrder); --2.3.3.2 Create New SOItem create table #NewSOItem ( SOItem_ID int not null, SourceItem_ID int not null ); insert into Table_SOItem ( SO_ID, SOItem_LineMajor, SOItem_LineMinor, Product_PN, SOItem_Description, SOItem_Quantity, SOItem_ListPrice, SOItem_AdjustedPrice, SOItem_UnitPrice, SOItem_Discount, SOItem_RequestDate, SOItem_ScheduledShipDate, SOItem_PromiseDate, SOItem_LineSet, SOItem_Status, SOItem_ReservedQty, SOItem_CancelledQty, SOItem_ShippedQty, SOItem_ShippedDate, SOItem_ShippingMethod, SOItem_FreightTerm, SOItem_ShippingInstructions, SOItem_PackingInstructions, SOItem_WaybillNumber, EndUserSite_ID, EndUserContact_ID, DeliveryToSite_ID, DeliveryToContact_ID, SOItem_CreditReason, SOItem_CreditUnitPrice, SOItem_AcceptedQty, Invoice_ID_ToBeDelete, SOItem_ServiceStartDate, SOItem_ServiceEndDate, SOItem_ServiceRefType, SOItem_ServiceRefNo, SOItem_CreditQty, SOItem_CreatedBy, SOItem_CreatedAt, SOItem_ModifiedBy, SOItem_ModifiedAt, SourceItem_ID, SOItem_VAT, SOItem_BoundleParentItemID, SOItem_VATCategory, SOItem_AppendService, ItemPricingAttr_ID ) output inserted.SOItem_ID, inserted.SourceItem_ID into #NewSOItem select NewSO.SO_ID, dense_rank() over(partition by SOI.SO_ID order by SOI.SOItem_LineMajor) as SOItem_LineMajor, row_number() over(partition by SOI.SO_ID,SOI.SOItem_LineMajor order by SOI.SOItem_LineMinor) as SOItem_LineMinor, SOI.Product_PN, SOI.SOItem_Description, SOI.SOItem_Quantity, SOI.SOItem_ListPrice, SOI.SOItem_AdjustedPrice, SOI.SOItem_UnitPrice, SOI.SOItem_Discount, SOI.SOItem_RequestDate, SOI.SOItem_ScheduledShipDate, SOI.SOItem_PromiseDate, SOI.SOItem_LineSet, SOI.SOItem_Status, SOI.SOItem_ReservedQty, SOI.SOItem_CancelledQty, SOI.SOItem_ShippedQty, SOI.SOItem_ShippedDate, SOI.SOItem_ShippingMethod, SOI.SOItem_FreightTerm, SOI.SOItem_ShippingInstructions, SOI.SOItem_PackingInstructions, SOI.SOItem_WaybillNumber, SOI.EndUserSite_ID, SOI.EndUserContact_ID, SOI.DeliveryToSite_ID, SOI.DeliveryToContact_ID, SOI.SOItem_CreditReason, SOI.SOItem_CreditUnitPrice, SOI.SOItem_AcceptedQty, SOI.Invoice_ID_ToBeDelete, SOI.SOItem_ServiceStartDate, SOI.SOItem_ServiceEndDate, SOI.SOItem_ServiceRefType, SOI.SOItem_ServiceRefNo, SOI.SOItem_CreditQty, N'SDC to PDC Migration' as SOItem_CreatedBy, getdate() as SOItem_CreatedAt, N'SDC to PDC Migration' as SOItem_ModifiedBy, getdate() as SOItem_ModifiedAt, SOI.SOItem_ID as SourceItem_ID, SOI.SOItem_VAT, SOI.SOItem_BoundleParentItemID, SOI.SOItem_VATCategory, SOI.SOItem_AppendService, SOI.ItemPricingAttr_ID from #NeedCreateSOItem NeedCreateSOItem inner join #NewSO NewSO on NeedCreateSOItem.SO_ID = NewSO.SO_SourceID inner join Table_SOItem SOI on NeedCreateSOItem.SOItem_ID = SOI.SOItem_ID where (cast(SOI.SOItem_ID as nvarchar) + SOI.Product_PN + N'SDC to PDC Migration') not in (select isnull(cast(SourceItem_ID as nvarchar), N'') + Product_PN + isnull(SOItem_CreatedBy, N'') from Table_SOItem);
如上代码中值得注意的有如下几点：
（1）insert语句中的output谓词，如下高亮处
由于Table_SalesOrder中的SO_ID列为identity列，所以在插入销售订单之前这些SO_ID都是未知的。但在下一步插入Table_SOItem时又需要引用这些SO_ID。这种情况下就可以使用output谓词从inserted虚表中得到新插入的SO_ID值。
（2）SO_SourceID的辅助作用
如上高亮显示处，我们output另一列SO_SourceID，其中的值为原先的SO_ID。SO_SourceID对于插入新的SOItem信息非常重要，我们是通过这列来找到原先对于的SOItem的，如下图所示
在我们这个例子中Table_SalesOrder中正好有一列SO_SourceID。但在有些表中没有类似的列，对于这种情况，可以先在原表上加一列存放该信息，在Migration之后再删除此列。
（3）用where条件来防止重复插入
在我们插入Table_SalesOrder和Table_SOItem时，我们加了where条件，如下高亮显示：
这些条件可以不加，但如果要使代码反复跑不会插入重复数据的话，就需要这些条件。这些条件是为了增加代码的健壮性。
需要注意的是，where条件中连接的值不能为null，否则会返回空集合。所以对于可能有null值的列需要用isnull进行转换。
（4）编号函数的使用
Table_SOItem有两个编号列，插入时需要重新编号，如下高亮显示
row_number()函数在做migration特别有用，大家也比较熟悉。对于dense_rank我们看一个小例子：
select dense_rank() over(order by field1), * from Table_Temp order by field1;
3.Open Inventory Migration部分代码分析
Open Inventory Migration中有一步是要把SDC下有库存的货物全出成SalesOrder。Table_InventoryBalance中的每一条记录会对应到一条SOItem。但业务上要求是不能把所用SOItem都归入一个SalesOrder（一个SalesOrder中的SOItem不能太多），要求一个SalesOrder中包含100条SOItem。要满足如上要求先看代码：
--0.Create temp table for need migration SDC inventory select IB.*, null as SOID into #NeedMigrateInventory from Table_InventoryBalance IB inner join Table_SubInventory SI on IB.SubInventory_ID = SI.SubInventory_ID where SI.Warehouse_ID = 1003 and IB.InvBalance_QtyOnHand > 0; --2.1.3 Insert SO Header declare @MaxSOItemQty int; set @MaxSOItemQty = 100; while exists(select * from #NeedMigrateInventory where SOID is null) begin ;With SubInventoryHasSDCInv as ( select SubInventory_ID from #NeedMigrateInventory where SOID is null group by SubInventory_ID ) insert into Table_SalesOrder ( Contact_ID, Customer_ID, Site_ID, SO_OrderDate, SO_OrderStatus, SO_CustomerPO, SO_OrderType, SO_RequestDate, SO_SalesPerson, SO_VAT, EndUserContact_ID, EndUserSite_ID, ShipToContact_ID, ShipToSite_ID, BillToContact_ID, BillToSite_ID, DeliveryToContact_ID, DeliveryToSite_ID, SO_PriceList, SO_CurrencyType, SO_LineSet, SO_PaymentTerm, SO_ShipPriority, SO_PaymentType, SO_ShippingMethod, SO_SalesChannel, SO_FreightTerm, SO_WinProbability, SO_IncoTerm, SO_SourceType, SO_SourceID, SO_WareHouse, SO_ShippingInstructions, SO_PackingInstructions, SO_CreatedBy, SO_CreatedAt, SO_ModifiedBy, SO_ModifiedAt, SO_Activity, SO_InvoicePrintoutType, SO_OrderNotes, SO_TaxStatus, SO_CForm, SO_OUID, SO_CFormReceived, SO_CFormReceivedBy, SO_CFormReceivedAt, SO_CFormNumber, SO_DCNo, SO_PONumber, SO_DCModifiedBy, SO_DCModifiedAt ) output inserted.SO_ID, inserted.SO_WareHouse into #NewCreatedSO select SOHeaderInfo.Contact_ID, SOHeaderInfo.Customer_ID, SOHeaderInfo.Site_ID, SOHeaderInfo.SO_OrderDate, SOHeaderInfo.SO_OrderStatus, SOHeaderInfo.SO_CustomerPO, SOHeaderInfo.SO_OrderType, SOHeaderInfo.SO_RequestDate, SOHeaderInfo.SO_SalesPerson, SOHeaderInfo.SO_VAT, SOHeaderInfo.EndUserContact_ID, SOHeaderInfo.EndUserSite_ID, SOHeaderInfo.ShipToContact_ID, SOHeaderInfo.ShipToSite_ID, SOHeaderInfo.BillToContact_ID, SOHeaderInfo.BillToSite_ID, SOHeaderInfo.DeliveryToContact_ID, SOHeaderInfo.DeliveryToSite_ID, SOHeaderInfo.SO_PriceList, SOHeaderInfo.SO_CurrencyType, SOHeaderInfo.SO_LineSet, SOHeaderInfo.SO_PaymentTerm, SOHeaderInfo.SO_ShipPriority, SOHeaderInfo.SO_PaymentType, SOHeaderInfo.SO_ShippingMethod, SOHeaderInfo.SO_SalesChannel, SOHeaderInfo.SO_FreightTerm, SOHeaderInfo.SO_WinProbability, SOHeaderInfo.SO_IncoTerm, SOHeaderInfo.SO_SourceType, SOHeaderInfo.SO_SourceID, SDCInv.SubInventory_ID SO_WareHouse, SOHeaderInfo.SO_ShippingInstructions, SOHeaderInfo.SO_PackingInstructions, SOHeaderInfo.SO_CreatedBy, SOHeaderInfo.SO_CreatedAt, SOHeaderInfo.SO_ModifiedBy, SOHeaderInfo.SO_ModifiedAt, SOHeaderInfo.SO_Activity, SOHeaderInfo.SO_InvoicePrintoutType, SOHeaderInfo.SO_OrderNotes, SOHeaderInfo.SO_TaxStatus, SOHeaderInfo.SO_CForm, SOHeaderInfo.SO_OUID, SOHeaderInfo.SO_CFormReceived, SOHeaderInfo.SO_CFormReceivedBy, SOHeaderInfo.SO_CFormReceivedAt, SOHeaderInfo.SO_CFormNumber, SOHeaderInfo.SO_DCNo, SOHeaderInfo.SO_PONumber, SOHeaderInfo.SO_DCModifiedBy, SOHeaderInfo.SO_DCModifiedAt from #SOHeaderInfo SOHeaderInfo, SubInventoryHasSDCInv SDCInv; With InvWithRN as ( select NeedMigrateInventory.InvBalance_ID, NewCreatedSO.SO_ID, row_number() over(partition by NeedMigrateInventory.SubInventory_ID order by NeedMigrateInventory.InvBalance_ID) as rn from #NeedMigrateInventory NeedMigrateInventory inner join #NewCreatedSO NewCreatedSO on NeedMigrateInventory.SubInventory_ID = NewCreatedSO.SO_WareHouse where NeedMigrateInventory.SOID is null ) update NeedMigrateInv set NeedMigrateInv.SOID = InvWithRN.SO_ID from #NeedMigrateInventory NeedMigrateInv inner join InvWithRN on NeedMigrateInv.InvBalance_ID = InvWithRN.InvBalance_ID where InvWithRN.rn <= @MaxSOItemQty; truncate table #NewCreatedSO; end
这段逻辑的主体思路是：把将转化为SOItem的Table_InventoryBalance相应记录放入临时表#NeedMigrateInventory中，并在临时表中加一辅助列SOID。在其后生成SalesOrder后，把生成的SO_ID放入辅助列SOID中。其中每个SalesOrder只有100条记录的要求是通过加row_number()辅助信息做到的。
