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

最近一直在忙公司里的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如下例所示：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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），我们直接看代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265

--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我们看一个小例子：

?

1 2 3 4 5 6 7

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。要满足如上要求先看代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182

--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()辅助信息做到的。