Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>配送中心数据迁移项目（SDC to PDC Data Migartion）

配送中心数据迁移项目（SDC to PDC Data Migartion）

时间：2010-08-27 来源：DBFocus

最近一直在忙公司里的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()辅助信息做到的。