全过程"/>
记一次数据迁移的全过程
背景
某大型项目的二期开发完成上线，需要初始化大量的数据源，并且第三方BPM系统提供的数据源也并非是完全按照我们所需要的格式、表结构给出的，所以需要做一次数据迁移。所用数据库为SqlServer，本次迁移思路可用于任何数据库间、表结构不一致的迁移。
前提及要求
- 明确本地数据迁移的范围
- 数据迁移中数据保持一致性
- 业务表增加迁移数据标识，业务表迁移数据字段ISMOVE='Y'
逻辑
由第三方提供数据源，经过清洗、过滤、写入到自己的业务库、业务表
实现
1.创建QY_Tables、Qy_Maping、Qy_FieldDic、Qy_ErrorMsg四张迁移设置表
--迁移表设置
CREATE TABLE QY_Tables
(ID INT NOT NULL -- 主键
, Type VARCHAR (255) NULL -- 类型【试图、表，一般皆为表】
, Business VARCHAR (255) NULL -- 业务模块
, TableName VARCHAR (255) NULL -- 表名中文
, TableCode VARCHAR (255) NULL -- 第三方提供的数据表表名【用于存储清洗后的数据】
, BPMTable VARCHAR (255) NULL -- 从第三方数据库同步过来之后的数据源表名[用于第一次同步第三方数据源到自己的库中]
, BPMVIEW VARCHAR (255) NULL -- 将第三方数据源表字段更换为中文所用试图，便于查看
, CostTable VARCHAR (255) NULL -- 自己的业务表
, Mark VARCHAR (255) NULL -- 备注
, Doing VARCHAR (255) NULL -- 是否同步
)--迁移字段设置表
--有可能QyTableFieldName字段在QyTableFieldCodeBPM中没有，那么设置QyDefaultValue默认值即可
--这个表，是设置我们所需要的业务字段，用于清洗数据使用
CREATE TABLE Qy_Maping
(Id INT IDENTITY NOT NULL --主键
, QyTableName NVARCHAR (50) NULL -- 迁移表名
, QyTableFieldName NVARCHAR (50) NULL -- 迁移字段中文名称
, QyTableFieldCode NVARCHAR (50) NULL -- 迁移字段英文
, QyTableFieldCodeBPM NVARCHAR (50) NULL -- 第三方迁移表字段英文
, QyRemark NVARCHAR (200) NULL --字段备注
, QyPOPTYPE NVARCHAR (200) NULL -- 下拉框字典，对应 Qy_FieldDic.POPTYPE
, QyPOPTYPEValueSouce NVARCHAR (50) NULL --下拉框字段具体值，对应 Qy_FieldDic.TITLEVALUE
, QyDefaultValue NVARCHAR (50) NULL -- 默认值
, QyRelaceNullValue NVARCHAR (50) NULL -- 如果第三方字段为空，默认替代值
, QyTableFieldType NVARCHAR (50) NULL -- 字段类型【'int'、'nvarchar'等】
, QyTableFieldDataType NVARCHAR (50) NULL -- 字段类型，一般如果是枚举值时不为空，其他为空
, QyIsNull NVARCHAR (50) NULL -- 是否为null
, QyIsNullBPM NVARCHAR (50) NULL -- 第三方数据源是否为null
, QyDeaultBPMValue NVARCHAR (50) NULL -- 第三方数据源默认值
)
GO-- 字段字典枚举值
CREATE TABLE dbo.Qy_FieldDic
(ID INT IDENTITY NOT NULL -- 主键
, POPUPLSTELEMENTID INT NULL -- 对应自己业务系统中已有的字段枚举值表ID，没有填0
, TITLECN NVARCHAR (50) NULL -- 中文含义
, TITLEVALUE NVARCHAR (50) NULL -- 编码
, POPTYPE NVARCHAR (50) NULL -- 枚举类型
, BPMCODE NVARCHAR (50) NULL -- 第三方数据源值编码
, BPMCLOUMN NVARCHAR (50) NULL -- 第三方数据源值对应的数据源列
, BPMTABLE NVARCHAR (50) NULL -- 所在迁移表表名
, CONSTRAINT PK_Qy_FieldDic PRIMARY KEY (ID)
)
GO--错误日志表，主要用于迁移失败的日志记录，可根据自己的业务自由扩展字段
CREATE TABLE dbo.Qy_ErrorMsg
(Id INT IDENTITY NOT NULL -- 主键
, FromTableName NVARCHAR (50) NULL -- 来源表
, ToTableName NVARCHAR (50) NULL -- 业务目标表
, ErrorMsg NVARCHAR (max) NULL -- 错误信息
, ErrorSqlCmd NVARCHAR (max) NULL -- 执行的sql语句
, CONSTRAINT PK_Qy_ErrorMsg PRIMARY KEY (Id)
)
GO2.BPM仅仅是提供了数据源的试图，并且数据源还在他们系统中，由于迁移量较大，所以先通过数据库桥接的形式，把他们提供的所有的数据源迁移到自己数据库的中间库[中间表]；通过VIEW_SQL_Proc_BPM_IN试图批量生成同步数据到自己中间库的sql语句，也可以将查询到的sql进一步分装成存储过程Proc_IN_BPM，这样执行起来同步更加方便。
create VIEW VIEW_SQL_Proc_BPM_IN AS
SELECT
'IF OBJECT_ID (''dbo.'+BPMTable+''') IS NOT NULL DROP TABLE dbo.'+BPMTable+';' AS SQL
FROM VIEW_QY_Tables
WHERE BPMTable <> ''
UNION ALLSELECT
'SELECT * INTO '+BPMTable+' from openquery(BPM, ''select * from sgbpm_prd.'+BPMTable + ''') TEMP;' AS SQL
FROM VIEW_QY_Tables
WHERE BPMTable <> ''
GO--中间如果有报错，根据实际情况更改下sql的查询方式即可
CREATE proc [dbo].[Proc_IN_BPM]
as
begin
--DROP TABLE bpm_fkjl_heyue_view;
DROP TABLE bpm_QY_BudgetMapKM_view ;
DROP TABLE bpm_QY_BudgetMapZT_view ;
DROP TABLE bpm_QY_BudgetZT_view ;
DROP TABLE bpm_QY_Company_view ;
DROP TABLE bpm_QY_ContractAppendices_view ;
DROP TABLE bpm_QY_ContractBasic_view ;
DROP TABLE bpm_QY_ContractCost_view ;
DROP TABLE bpm_QY_ContractMatter_view ;
DROP TABLE bpm_QY_ContractParty_view ;
DROP TABLE bpm_QY_ContractPayment_view ;
DROP TABLE bpm_QY_ContractType_view ;
DROP TABLE bpm_QY_Department_view ;
DROP TABLE bpm_QY_Legal_view ;
DROP TABLE bpm_QY_LX_Basic_view ;
DROP TABLE bpm_QY_LX_File_view ;
DROP TABLE bpm_QY_LX_Release_view ;
DROP TABLE bpm_QY_LX_Subject_view ;
DROP TABLE bpm_QY_Person_view ;
DROP TABLE bpm_QY_Project_view ;
DROP TABLE bpm_QY_Provision_gys_view ;
DROP TABLE bpm_QY_Provision_jtmx_view ;
DROP TABLE bpm_QY_Provision_view ;
DROP TABLE bpm_QY_Subject_view ;
DROP TABLE bpm_QY_YS_File_view ;
DROP TABLE bpm_QY_YS_PayPlan_view ;
DROP TABLE bpm_QY_YS_Settlement_view ;
DROP TABLE bpm_QY_YS_Subject_view ;
DROP TABLE bpm_QY_YS_view ;--DROP TABLE BPM_JITICHONGXIAO_NEW_VIEW ;
--DROP TABLE BPM_JITICHONGXIAOMAIN_NEW_VIEW ;
--DROP TABLE bpm_toheyue_htfkjlkemu_view;
--DROP TABLE bpm_lxiangyanshoukemu_view;
--DROP TABLE bpm_htyufukuankemu_view;SELECT * INTO bpm_QY_ContractType_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractType_view') TEMP;
SELECT * INTO bpm_QY_Legal_view
from openquery(BPM,'select
NBFRZTID, GSMC, NSRSBH, FDDLR, GSDZ, YZBM, LXDH, BZ, NBFRBH, KAIHUHANG, KAIHUMINGCHENG, KAIHUZHUANGHAO, CJR, CJSJ,XGR, XGSJ, LIANHANCODE, AREACODEINFO, REC_PROVINCE, REC_CITY, USAGE, TYPES ,cast(RecordStatus AS varchar(10)) AS RecordStatus
from sgbpm_prd.bpm_QY_Legal_view') a;SELECT * INTO bpm_QY_Subject_view from openquery(BPM, 'select
YUSUANKEMUID ,
YUSUANKEMUMINGCHENG ,
YUSUANKEMUBIANHAO ,
FUJIKEMUID ,
KEMUJIBIE ,
cast(SHIFOUMOJIKEMU AS varchar(10)) AS SHIFOUMOJIKEMU,
CHANNELTYPE ,
ISBAOXIAO ,
COSTTYPE ,
ISCHECKEXTRACT ,
ISCHECKPACT ,
ISHOUSE ,
ISNEGATIVE ,
ISKEMUADJUST ,
MINLXZHAOBIAOJINE ,
MAXFEIHETONGLXJINE ,
MAXLXYANSHOUJINE ,
YUSUANLEIXING ,
PROJECTTYPE ,
SUBJECTTYPE ,
ASSESSMENT_TYPE ,
YUSUANKEMUSHUILV ,
GXMC ,
YUSUANKEMUSHUOMING ,
XIUGAIREN ,
XIUGAISHIJIAN ,
cast(RECORDSTATUS AS varchar(10)) AS RECORDSTATUSfrom sgbpm_prd.bpm_QY_Subject_view') TEMP;
SELECT * INTO bpm_QY_BudgetMapKM_view from openquery(BPM,'select YUSUANKEMUID ,
NEWKMMC ,
NEWKMBH ,
CHANNELTYPE ,
COSTTYPE ,
PROJECTTYPE ,
SUBJECTTYPE ,
OLDKMMC ,
OLDKMBH ,
CJR ,
CJSJ ,
XGR ,
XGSJ ,
cast(RecordStatus AS varchar(10)) AS RecordStatusfrom sgbpm_prd.bpm_QY_BudgetMapKM_view') a;
SELECT * INTO bpm_QY_Project_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_Project_view') TEMP;
SELECT * INTO bpm_QY_BudgetZT_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_BudgetZT_view') TEMP;
SELECT * INTO bpm_QY_BudgetMapZT_view from openquery(BPM, 'select
ID, OLDZTBH, OLDZTMC, NEWZTID, NEWZTBH, NEWZTMC, CJR, CJSJ, XGR, XGSJ, TYPE, STATUS, TYPENAME
,
cast(RecordStatus AS varchar(10)) AS RecordStatusfrom sgbpm_prd.bpm_QY_BudgetMapZT_view') TEMP;SELECT * INTO bpm_QY_ContractBasic_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractBasic_view') TEMP;
SELECT * INTO bpm_QY_ContractParty_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractParty_view') TEMP;
SELECT * INTO bpm_QY_ContractPayment_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractPayment_view') TEMP;
SELECT * INTO bpm_QY_ContractAppendices_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractAppendices_view') TEMP;
SELECT * INTO bpm_QY_ContractCost_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractCost_view') TEMP;
SELECT * INTO bpm_QY_YS_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_YS_view') TEMP;
SELECT * INTO bpm_QY_YS_PayPlan_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_YS_PayPlan_view') TEMP;
SELECT * INTO bpm_QY_YS_Subject_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_YS_Subject_view') TEMP;
SELECT * INTO bpm_QY_YS_Settlement_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_YS_Settlement_view') TEMP;
SELECT * INTO bpm_QY_YS_File_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_YS_File_view') TEMP;--SELECT * INTO bpm_fkjl_heyue_view from openquery(BPM, 'select * from sgbpm_prd.bpm_fkjl_heyue_view') TEMP;SELECT * INTO bpm_QY_LX_Basic_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_LX_Basic_view') TEMP;
SELECT * INTO bpm_QY_LX_Subject_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_LX_Subject_view') TEMP;
SELECT * INTO bpm_QY_LX_File_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_LX_File_view') TEMP;
SELECT * INTO bpm_QY_LX_Release_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_LX_Release_view') TEMP;SELECT * INTO bpm_QY_ContractMatter_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_ContractMatter_view') TEMP;
SELECT * INTO bpm_QY_Provision_gys_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_Provision_gys_view') TEMP;
SELECT * INTO bpm_QY_Provision_jtmx_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_Provision_jtmx_view') TEMP;
SELECT * INTO bpm_QY_Provision_view from openquery(BPM, 'select * from sgbpm_prd.bpm_QY_Provision_view') TEMP;SELECT * INTO bpm_QY_Department_view from VIEW_SS_bpm_QY_Department_view TEMP;
SELECT * INTO bpm_QY_Company_view from VIEW_SS_bpm_QY_Company_view TEMP;
SELECT * INTO bpm_QY_Person_view from VIEW_EAS_PERSON TEMP;--SELECT * INTO BPM_JITICHONGXIAO_NEW_VIEW from openquery(BPM,'select * from sgbpm_prd.BPM_JITICHONGXIAO_NEW_VIEW where xmbh
--IN(''HSHS379'',''ZJZJ457'',''XHS288'',''HSHS370'') and ZTLX=''1''') a;
--SELECT * INTO BPM_JITICHONGXIAOMAIN_NEW_VIEW from openquery(BPM,'select * from sgbpm_prd.BPM_JITICHONGXIAOMAIN_NEW_VIEW') a;--SELECT
--* INTO bpm_toheyue_htfkjlkemu_view
-- from openquery(BPM,'select * from sgbpm_prd.bpm_toheyue_htfkjlkemu_view') a;
--SELECT * FROM QY_Project WHERE [项目名称] IN ('成都温江合生汇','北京朝阳合生汇','广州海珠合生广场南区','广州海珠合生广场北区')--SELECT * INTO bpm_lxiangyanshoukemu_view from openquery(BPM,'select * from sgbpm_prd.bpm_lxiangyanshoukemu_view') a;--SELECT * INTO bpm_htyufukuankemu_view from openquery(BPM,'select * from sgbpm_prd.bpm_htyufukuankemu_view') a;end
GO3.执行完上述的Proc_IN_BPM存储过程，我们会发现自己的中间库中多了很多QY_Tables表中BPMTable列下的表，这就是我们同步过来的数据源表，后续所有操作都是在这些表上做处理
4.清洗过程：
- 编写试图VIEW_SQL_Proc_IN，得到第三方数据源中文字段的数据源表，得到中文字段主要目的是方便数据核实
create VIEW VIEW_SQL_Proc_IN AS SELECT 'CREATE proc Proc_IN_'+QyTableName+' AS begin INSERT INTO '+QyTableName+ ' ('+LEFT(BPMList2,LEN(BPMList2)-1)+') '+ ' SELECT ' +LEFT(BPMList,LEN(BPMList)-1) + ' FROM '+BPMVIEW + ' END GO ' AS SQL2 FROM (SELECT QyTableName,BPMTable,BPMVIEW,(SELECT SQL1+',' FROM VIEW_CheckSQLWHERE QyTableName=A.QyTableNameFOR XML PATH('')) AS BPMList,(SELECT QyTableFieldName+',' FROM VIEW_CheckSQLWHERE QyTableFieldCodeBPM IS NOT NULL AND QyTableName=A.QyTableNameFOR XML PATH('')) AS BPMList2FROM VIEW_CheckSQL A--WHERE QyTableName='QY_ContractType'GROUP BY QyTableName,BPMTable,BPMVIEW )TEMP WHERE BPMList <> '' GOcreate VIEW VIEW_CheckSQL ASSELECT T.Business,T.TableName,T.BPMTable,T.BPMVIEW,M.QyTableName,M.QyTableFieldName,M.QyTableFieldCodeBPM,M.QyIsNull,M.QyIsNullBPM,CASE WHEN M.QyDeaultBPMValue IS NULL THEN M.QyTableFieldCodeBPM+ +' AS ['+M.QyTableFieldName+']'ELSE 'ISNULL('+M.QyTableFieldCodeBPM+','''+M.QyDeaultBPMValue+''')'+' AS ['+M.QyTableFieldName+']'END AS SQL1FROMVIEW_Qy_Maping MLEFT JOIN VIEW_QY_Tables T ON T.TableCode=M.QyTableNameWHERE QyTableFieldName IS NOT NULL GO - 查询VIEW_SQL_Proc_IN会得到所有表的一个存储过程的执行语句，把查询结果复制出来之后，批量执行，继而封装成存储过程
CREATE proc [Proc_IN_QY] as BEGIN DELETE FROM QY_BudgetMapKM ; DELETE FROM QY_BudgetMapZT ; DELETE FROM QY_BudgetZT ; DELETE FROM QY_ContractAppendices ; DELETE FROM QY_ContractBasic ; DELETE FROM QY_ContractCost ; DELETE FROM QY_ContractParty ; DELETE FROM QY_ContractPayment ; DELETE FROM QY_ContractType ; --DELETE FROM QY_Department ; --DELETE FROM QY_Legal ; DELETE FROM QY_LX_Basic ; DELETE FROM QY_LX_File ; DELETE FROM QY_LX_Release ; DELETE FROM QY_LX_Subject ; DELETE FROM QY_Project ; DELETE FROM QY_ProjectLegal ; DELETE FROM QY_Subject ; DELETE FROM QY_YS ; DELETE FROM QY_YS_File ; DELETE FROM QY_YS_PayPlan ; DELETE FROM QY_YS_Settlement ; DELETE FROM QY_YS_Subject ; DELETE FROM QY_YS_Pay ;DELETE FROM QY_Provision ; DELETE FROM QY_Provision_jtmx ; DELETE FROM QY_Provision_gys ; DELETE FROM QY_ContractMatter ;DELETE FROM QY_Company ; --DELETE FROM QY_Person ; --DELETE FROM QY_ProjectLegal ; DELETE FROM QY_ProjectMap ; --DELETE FROM QY_JITICHONGXIAOMAIN ;exec Proc_IN_QY_BudgetMapKM ; exec Proc_IN_QY_BudgetMapZT ; exec Proc_IN_QY_BudgetZT ; exec Proc_IN_QY_ContractAppendices ; exec Proc_IN_QY_ContractBasic ; exec Proc_IN_QY_ContractCost ; exec Proc_IN_QY_ContractParty ; exec Proc_IN_QY_ContractPayment ; exec Proc_IN_QY_ContractType ; --exec Proc_IN_QY_Legal ; exec Proc_IN_QY_LX_Basic ; exec Proc_IN_QY_LX_File ; exec Proc_IN_QY_LX_Release ; exec Proc_IN_QY_LX_Subject ; exec Proc_IN_QY_Project ; exec Proc_IN_QY_Subject ; exec Proc_IN_QY_YS ; exec Proc_IN_QY_YS_File ; exec Proc_IN_QY_YS_PayPlan ; exec Proc_IN_QY_YS_Settlement ; exec Proc_IN_QY_YS_Subject ; exec Proc_IN_QY_YS_Payexec Proc_IN_QY_ContractMatter ; exec Proc_IN_QY_Provision ; exec Proc_IN_QY_Provision_jtmx ; exec Proc_IN_QY_Provision_gys ;--exec Proc_IN_QY_Department ; --exec Proc_IN_QY_Person ;exec Proc_IN_QY_Company ; --exec Proc_IN_QY_ProjectLegal ; exec Proc_IN_QY_ProjectMap ; --exec Proc_IN_QY_JITICHONGXIAOMAIN ;exec Proc_BPM_Clear ; end GO -
执行Proc_IN_QY存储过程之后，我们会得到所有迁移数据源的中文数据源表，主要目的是为了方便数据源查看
-
接下来编写试图VIEW_SQL_Proc_OUT，我们把当前中间库的数据源写入到目标库
CREATE VIEW VIEW_SQL_Proc_OUT ASSELECT 'if OBJECT_ID(''dbo.PROC_OUT_'+TableCode+''',''procedure'') is not null BEGIN DROP PROC dbo.PROC_OUT_' + TableCode + ' END GO' AS SQL1 FROM VIEW_QY_Tables WHERE BPMTable <> '' UNION SELECT 'CREATE proc Proc_OUT_'+TableCode+' AS begin INSERT INTO [HY].[HopsonCost].[dbo].'+TableCode+ ' SELECT * FROM '+TableCode + ' END GO ' AS SQL1 FROM VIEW_QY_Tables WHERE BPMTable <> '' GO -
执行VIEW_SQL_Proc_OUT会得所有写出的存储过程代码，粘贴复制出来，进行执行
-
同样，将上述的存储过程打包进Proc_OUT_QY，便于执行
CREATE proc [dbo].[Proc_OUT_QY] as beginDELETE FROM [HY].[HopsonCost].[dbo].QY_BudgetMapKM; DELETE FROM [HY].[HopsonCost].[dbo].QY_BudgetMapZT; DELETE FROM [HY].[HopsonCost].[dbo].QY_BudgetZT; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractAppendices; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractBasic; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractCost; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractParty; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractPayment; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractType; --DELETE FROM [HY].[HopsonCost].[dbo].QY_Department; --DELETE FROM [HY].[HopsonCost].[dbo].QY_Legal; DELETE FROM [HY].[HopsonCost].[dbo].QY_LX_Basic; DELETE FROM [HY].[HopsonCost].[dbo].QY_LX_File; DELETE FROM [HY].[HopsonCost].[dbo].QY_LX_Release; DELETE FROM [HY].[HopsonCost].[dbo].QY_LX_Subject; DELETE FROM [HY].[HopsonCost].[dbo].QY_Project; --DELETE FROM [HY].[HopsonCost].[dbo].QY_ProjectLegal; DELETE FROM [HY].[HopsonCost].[dbo].QY_Subject; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS_File; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS_PayPlan; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS_Settlement; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS_Subject; DELETE FROM [HY].[HopsonCost].[dbo].QY_YS_Pay; DELETE FROM [HY].[HopsonCost].[dbo].QY_Company; --DELETE FROM [HY].[HopsonCost].[dbo].QY_Person; --DELETE FROM [HY].[HopsonCost].[dbo].QY_ProjectLegal; DELETE FROM [HY].[HopsonCost].[dbo].QY_ProjectMap; DELETE FROM [HY].[HopsonCost].[dbo].QY_ContractMatter; DELETE FROM [HY].[HopsonCost].[dbo].QY_Provision; DELETE FROM [HY].[HopsonCost].[dbo].QY_Provision_jtmx; DELETE FROM [HY].[HopsonCost].[dbo].QY_Provision_gys; --DELETE FROM [HY].[HopsonCost].[dbo].QY_JITICHONGXIAOMAIN ;exec Proc_OUT_QY_BudgetMapKM ; exec Proc_OUT_QY_BudgetMapZT ; exec Proc_OUT_QY_BudgetZT ; exec Proc_OUT_QY_ContractAppendices ; exec Proc_OUT_QY_ContractBasic ; exec Proc_OUT_QY_ContractCost ; exec Proc_OUT_QY_ContractParty ; exec Proc_OUT_QY_ContractPayment ; exec Proc_OUT_QY_ContractType ; --exec Proc_OUT_QY_Legal ; exec Proc_OUT_QY_Project ; exec Proc_OUT_QY_Subject ; exec Proc_OUT_QY_YS ; exec Proc_OUT_QY_YS_File ; exec Proc_OUT_QY_YS_PayPlan ; exec Proc_OUT_QY_YS_Settlement ; exec Proc_OUT_QY_YS_Subject ; exec Proc_OUT_QY_YS_Pay; exec Proc_OUT_QY_LX_Basic ; exec Proc_OUT_QY_LX_Subject ; exec Proc_OUT_QY_LX_File ; exec Proc_OUT_QY_LX_Release ; --exec Proc_OUT_QY_Department ; --exec Proc_OUT_QY_ProjectLegal ; exec Proc_OUT_QY_Company ; --exec Proc_OUT_QY_Person ; exec Proc_OUT_QY_ProjectMap ; exec Proc_OUT_QY_ContractMatter ; exec Proc_OUT_QY_Provision ; exec Proc_OUT_QY_Provision_gys ; exec Proc_OUT_QY_Provision_jtmx ; --exec Proc_OUT_QY_JITICHONGXIAOMAIN ;--DROP TABLE [HY].[HopsonCost].[dbo].bpm_fkjl_heyue_view; --DROP TABLE [HY].[HopsonCost].[dbo].bpm_toheyue_htfkjlkemu_view;--SELECT * INTO [HY].[HopsonCost].[dbo].bpm_fkjl_heyue_view FROM bpm_fkjl_heyue_view; --SELECT * INTO [HY].[HopsonCost].[dbo].bpm_toheyue_htfkjlkemu_view FROM bpm_toheyue_htfkjlkemu_view;--DROP TABLE [HY].[HopsonCost].[dbo].bpm_htyufukuankemu_view; --DROP TABLE [HY].[HopsonCost].[dbo].bpm_lxiangyanshoukemu_view;--SELECT * INTO [HY].[HopsonCost].[dbo].bpm_htyufukuankemu_view FROM bpm_htyufukuankemu_view; --SELECT * INTO [HY].[HopsonCost].[dbo].bpm_lxiangyanshoukemu_view FROM bpm_lxiangyanshoukemu_view;end GO -
执行完上述存储过程之后，我们会发现，在我们的目标库中生成了我们的中文字段的数据源表
5.数据写入
1.根据得到的数据源表，可以进行code代码，结合Qy_FieldDic、Qy_ErrorMsg进行相关字段字典的转换以及日志记录
2.某个业务的代码介绍
/// <summary>
/// 付款申请数据迁移
/// 1.1 主表记录
/// </summary>
/// <returns></returns>
public virtual HttpResponseMessage UpPaymentRequest()
{try{int totalCount = 0, successCount = 0, errorCout = 0;string fromTableName = "QY_PaymentRequest";string toTableName = "FM_PaymentRequest";string qyBUSSID = "PaymentRequestBussID";string qyCreateUserID = "CreateUserID";string qyModifyUserID = "ModifyUserID";string qyProjectID = "ProjectID";string qyContractID = "ContractID";string qyProAppID = "ProAppID";string qyImprestID = "ImprestID";using (var context = new MasterDbContext()){List<Qy_Maping> mapings = context.Qy_Maping.Where(ss => ss.QyTableName == fromTableName).ToList();List<Qy_FieldDic> fieldDics = context.Qy_FieldDic.Where(x=>x.BPMTABLE == fromTableName).ToList();var BUSSID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyBUSSID);if (BUSSID == null){return WebApiHelper.GetErrorHttpResponseMessage(new { Messsage = "不存在业务ID" });}DataTable dataTable = context.ExecuteDataTable("select * from " + fromTableName);totalCount = dataTable.Rows.Count;LogTxtInfo("需要迁移" + totalCount + "条");var CreateUserID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyCreateUserID);var ModifyUserID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyModifyUserID);var ProjectID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyProjectID);var ProAppID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyProAppID);var ContractID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyContractID);var ImprestID = mapings.FirstOrDefault(ss => ss.QyTableFieldCode == qyImprestID);for (int i = 0; i < dataTable.Rows.Count; i++){DataRow row = dataTable.Rows[i];object obj = row[BUSSID.QyTableFieldName];if (obj == null){//记录日志errorCout++;WriteLog(context, fromTableName, toTableName, "业务ID为空");continue;}object objCreateUserID = row[CreateUserID.QyTableFieldName];object objModifyUserID = row[ModifyUserID.QyTableFieldName];object objProjectID = row[ProjectID.QyTableFieldName];object objProAppID = row[ProAppID.QyTableFieldName];string objContractID = row[ContractID.QyTableFieldName].ToStr();string objImprestID = row[ImprestID.QyTableFieldName].ToStr();int ProBUSSID = 0;if (objProjectID != null){ProBUSSID = context.Database.SqlQuery<int>("SELECT ProjectID FROM dbo.CMN_Project WHERE ProBUSSID = '" + objProjectID.ToString() + "'").FirstOrDefault();ProjectID.QyDefaultValue = ProBUSSID.ToStr();}int Plixiangid = 0;if(ProAppID != null){Plixiangid = context.Database.SqlQuery<int>("SELECT ProAppID FROM CMN_SyncProjectApproval WHERE ProAppBUSSID = '" + objProAppID + "'").FirstOrDefault();ProAppID.QyDefaultValue = Plixiangid.ToString();}int CUserID = 0;if (objCreateUserID != null){CUserID = context.Database.SqlQuery<int>("SELECT USERID FROM dbo.CF_USER WHERE USERNO = '" + objCreateUserID + "'").FirstOrDefault();CreateUserID.QyDefaultValue = CUserID.ToString();}int MUserID = 0;if (objCreateUserID != null){MUserID = context.Database.SqlQuery<int>("SELECT USERID FROM dbo.CF_USER WHERE USERNO = '" + objModifyUserID + "'").FirstOrDefault();ModifyUserID.QyDefaultValue = MUserID.ToString();}if (objContractID != "") {var tempContract = context.CON_Contract.FirstOrDefault(x => x.ContractIDBUSSID == objContractID);if (tempContract != null){ContractID.QyDefaultValue = tempContract.ContractID.ToStr();}else {LogTxtInfo("迁移失败： 未找到合同信息：objContractID=" + objContractID);//记录日志errorCout++;WriteLog(context, fromTableName, toTableName, "迁移失败： 未找到合同信息：objContractID=" + objContractID);continue;}}if (objImprestID != "") {var tempImprest = context.FM_Imprest.FirstOrDefault(x => x.BPMmainId == objImprestID);if (tempImprest != null){ImprestID.QyDefaultValue = tempImprest.ImprestID.ToStr();}else{LogTxtInfo("迁移失败： 未找到备用金信息：objImprestID=" + objImprestID);//记录日志errorCout++;WriteLog(context, fromTableName, toTableName, "迁移失败： 未找到备用金信息：objImprestID=" + objImprestID);continue;}}string updateWhere = string.Empty;if (obj.ToString() == "NULL"){updateWhere = " WHERE 1=2";}else{updateWhere = " WHERE " + qyBUSSID + "='" + obj.ToString() + "'";}string sqlCmdInsert = string.Empty;string sqlCmdUpdate = string.Empty;try{using (var dbContextTransaction = context.Database.BeginTransaction()){sqlCmdInsert = CreateInsertSqlCmd(row, toTableName, mapings, fieldDics);sqlCmdUpdate = CreateUpdateSqlCmd(row, toTableName, mapings, fieldDics) + updateWhere;List<SqlParameter> paramArray = new List<SqlParameter>();paramArray.Add(new SqlParameter("@bussid", obj.ToString()));paramArray.Add(new SqlParameter("@insertCmd", sqlCmdInsert));paramArray.Add(new SqlParameter("@updateCmd", sqlCmdUpdate));paramArray.Add(new SqlParameter("@tableName", toTableName));context.Database.ExecuteSqlCommand("[dbo].[Qy_InsertOrUpdateAndInsertPrimarykeyRelation] @bussid , @insertCmd , @updateCmd , @tableName ", paramArray.ToArray());dbContextTransaction.Commit();successCount++;}}catch (Exception ex){//记录日志errorCout++;WriteLog(context, fromTableName, toTableName, ex.ToString(), sqlCmdInsert, sqlCmdUpdate);continue;}}}LogTxtInfo("迁移完毕,总计：" + totalCount + "条，成功：" + successCount + "条，失败：" + errorCout + "条");return WebApiHelper.GetOkHttpResponseMessage(new { Messsage = "迁移完毕,总计：" + totalCount + "条，成功：" + successCount + "条，失败：" + errorCout + "条" });}catch (Exception ex){LogTxtInfo("迁移异常：" + ex.Message.ToString());return WebApiHelper.GetErrorHttpResponseMessage(new { Messsage = ex.Message.ToString() });}
}
/// <summary>
/// 创建插入数据SQL
/// </summary>
/// <param name="dataRow"></param>
/// <param name="tableName"></param>
/// <param name="mapings"></param>
/// <param name="fieldDics"></param>
/// <returns></returns>
private string CreateInsertSqlCmd(DataRow dataRow, string tableName, List<Qy_Maping> mapings, List<Qy_FieldDic> fieldDics)
{mapings = mapings.Where(ss => ss.QyTableFieldCode != null).ToList();StringBuilder sqlAppend = new StringBuilder();sqlAppend.Append("INSERT INTO " + tableName + "");sqlAppend.Append("( ");List<string> fields = new List<string>();foreach (var item in mapings){fields.Add(item.QyTableFieldCode);}sqlAppend.Append(string.Join(",", fields));sqlAppend.Append(")");sqlAppend.Append("VALUES ");sqlAppend.Append("( ");List<string> values = new List<string>();for (int k = 0; k < mapings.Count; k++){Qy_Maping maping = mapings[k];try{string QyTableFieldName = maping.QyTableFieldName;if (string.IsNullOrEmpty(QyTableFieldName)){if (string.IsNullOrEmpty(maping.QyDefaultValue)){values.Add("NULL");}else{//values.Add("'" + mapings[k].QyDefaultValue + "'");values.Add(GetFieldTypeForValue(maping.QyTableFieldType, maping.QyDefaultValue));}}else{//如果设置默认值，则强制覆盖if (!string.IsNullOrEmpty(maping.QyDefaultValue)){//values.Add("'" + mapings[k].QyDefaultValue + "'");values.Add(GetFieldTypeForValue(maping.QyTableFieldType, maping.QyDefaultValue));}else{object obj = dataRow[QyTableFieldName];if (obj != null && !string.IsNullOrEmpty(obj.ToString())){string value = GetValue(obj, maping.QyPOPTYPE, maping.QyPOPTYPEValueSouce, fieldDics);if (value == "NULL")values.Add(value);else//values.Add(value);values.Add(GetFieldTypeForValue(maping.QyTableFieldType, value));}else{if (!string.IsNullOrEmpty(maping.QyRelaceNullValue)){values.Add(GetFieldTypeForValue(maping.QyTableFieldType, maping.QyRelaceNullValue));}elsevalues.Add(" NULL ");}}}}catch (Exception ex){throw new Exception("错误字段：" + maping.QyTableFieldName + ",错误消息：" + ex.Message);}}sqlAppend.Append(string.Join(",", values));sqlAppend.Append(")");return sqlAppend.ToString();
}/// <summary>
/// 创建更新数据SQL
/// </summary>
/// <param name="dataRow"></param>
/// <param name="tableName"></param>
/// <param name="mapings"></param>
/// <param name="fieldDics"></param>
/// <returns></returns>
private string CreateUpdateSqlCmd(DataRow dataRow, string tableName, List<Qy_Maping> mapings, List<Qy_FieldDic> fieldDics)
{mapings = mapings.Where(ss => ss.QyTableFieldCode != null).ToList();List<string> sqlCmdUpdateTabel = new List<string>();StringBuilder sqlAppend = new StringBuilder();sqlAppend.Append("UPDATE " + tableName + " SET ");List<string> values = new List<string>();for (int k = 0; k < mapings.Count; k++){Qy_Maping maping = mapings[k];try{string QyTableFieldName = maping.QyTableFieldName;if (string.IsNullOrEmpty(QyTableFieldName)){if (string.IsNullOrEmpty(maping.QyDefaultValue)){values.Add(maping.QyTableFieldCode + "= NULL ");}else{values.Add(mapings[k].QyTableFieldCode + " = " + GetFieldTypeForValue(maping.QyTableFieldType, maping.QyDefaultValue));}}else{//如果设置默认值，则强制覆盖if (!string.IsNullOrEmpty(maping.QyDefaultValue)){values.Add(maping.QyTableFieldCode + "= " + GetFieldTypeForValue(maping.QyTableFieldType, maping.QyDefaultValue));}else{object obj = dataRow[QyTableFieldName];if (obj != null && !string.IsNullOrEmpty(obj.ToString())){string value = GetValue(obj, maping.QyPOPTYPE, maping.QyPOPTYPEValueSouce, fieldDics);if (value == "NULL")values.Add(maping.QyTableFieldCode + "= NULL ");elsevalues.Add(maping.QyTableFieldCode + " = " + GetFieldTypeForValue(maping.QyTableFieldType, value));}else{if (!string.IsNullOrEmpty(maping.QyRelaceNullValue)){values.Add(maping.QyTableFieldCode + " = " + GetFieldTypeForValue(maping.QyTableFieldType, maping.QyRelaceNullValue));}elsevalues.Add(maping.QyTableFieldCode + "= NULL ");}}}}catch (Exception ex){throw new Exception("错误字段：" + maping.QyTableFieldName + ",错误消息：" + ex.Message);}}sqlAppend.Append(string.Join(",", values));return sqlAppend.ToString();
}/// <summary>
/// 根据字段类型创建SQL语句的Values
/// </summary>
/// <param name="QyTableFieldType"></param>
/// <param name="Value"></param>
/// <returns></returns>
private string GetFieldTypeForValue(string QyTableFieldType, string Value)
{try{if (string.IsNullOrEmpty(QyTableFieldType))return "'" + Value + "'";string fieldValue = string.Empty;switch (QyTableFieldType){case "char": fieldValue = "'" + Value + "'"; break;case "date": fieldValue = "'" + Value + "'"; break;case "datetime": fieldValue = "'" + Convert.ToDateTime(Value).ToString("yyyy-MM-dd HH:mm:ss") + "'"; break;case "int": fieldValue = Convert.ToInt32(Value).ToString(); break;case "decimal": fieldValue = Convert.ToDecimal(Value).ToString(); break;case "nvarchar": fieldValue = "'" + Value.Replace("'", "’") + "'"; break;case "VARCHAR2": fieldValue = "'" + Value.Replace("'", "’") + "'"; break;case "NUMBER": fieldValue = Convert.ToInt32(Value).ToString(); break;case "NUMBER(20,4)": fieldValue = Convert.ToDecimal(Value).ToString(); break;case "TIMESTAMP": fieldValue = "to_timestamp('" + Convert.ToDateTime(Value).ToString() + "','YYYY-MM-DDHH24:MI:SS')"; break;case "DATE": fieldValue = "to_date('" + Convert.ToDateTime(Value).ToString() + "','YYYY-MM-DDHH24:MI:SS')"; break;default: fieldValue = "'" + Value + "'"; break;}return fieldValue;}catch (Exception ex){throw new Exception(ex.Message);}
}/// <summary>
/// 记录错误日志
/// </summary>
/// <param name="context"></param>
/// <param name="fromTable"></param>
/// <param name="toTable"></param>
/// <param name="errorMsg"></param>
/// <param name="sqlCmdInsert"></param>
/// <param name="sqlCmdUpdate"></param>
private void WriteLog(MasterDbContext context, string fromTable, string toTable, string errorMsg, string sqlCmdInsert = "", string sqlCmdUpdate = "")
{string sqlCmdLog = "INSERT INTO[dbo].[Qy_ErrorMsg]([FromTableName],[ToTableName],[ErrorMsg],[ErrorInsertSqlCmd],[ErrorUpdateSqlCmd]) VALUES(@FromTableName,@ToTableName,@ErrorMsg,@ErrorInsertSqlCmd,@ErrorUpdateSqlCmd)";List<SqlParameter> paramArray = new List<SqlParameter>();paramArray.Add(new SqlParameter("@FromTableName", fromTable));paramArray.Add(new SqlParameter("@ToTableName", toTable));paramArray.Add(new SqlParameter("@ErrorMsg", errorMsg));paramArray.Add(new SqlParameter("@ErrorInsertSqlCmd", sqlCmdInsert));paramArray.Add(new SqlParameter("@ErrorUpdateSqlCmd", sqlCmdUpdate));context.Database.ExecuteSqlCommand(sqlCmdLog, paramArray.ToArray());
}总结
至此，整体的迁移完成。迁移过程中，其实没有太多难点，只是更多的需要耐心和细心。同样，上述的迁移思路也可以进行简化。只不过，个人认为，这样做更安全、更便于后期数据的核实等工作。当然，还有其他更多的一些方案，欢迎一起交流~~~
更多推荐
记一次数据迁移的全过程
发布评论