使用SqlBulkCopy类结合DataTable类快速完成对目标为SQLSERVER的数据交换
Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。
需求
需要完成一个多张表的数据交换，源数据库为oracle，目标为SqlServer。
源表分几个业务块，每一块有多张表，其中一张主表，其余若干子表。
主表主键和子表关联外键为字符型，据查是GUID字符串。
目标表业务模块基本一致。
主表主键和子表关联外键为数字型，据查是INT32类型。
分析
源和目标表的数据内容基本相似，最大区别为主键不一致，导致源表主键无法放入目标表，目标每条记录的主键必须重新赋值。
源表和目标表一些不一致的字段类型，比如date到varchar(8)，varchar2(1)到bit等。
解决方法
由于SqlBulkCopy类能够批量插入数据到SqlServer，故采用该类作为数据交换。
源主到目标主表时，目标主键需要动态生成，同时目标子表的外键也需和主表主键对应。
思路如下：
先取得主表数据，然后生成DataTable类，附加一列动态生成目标主键列（轮询所有行，值从1到该DataTable的行数）。执行主表数据的插入。
依次取得子表所有数据DataTable，每张子表先增加一列，轮询所有行，按源外键关联主表DataTable找到动态生成的目标主键列，赋值给增加的那列。对新生成的子表DataTable依次插入目标子表。
源码
配置文件：
主表的标志，子表的标志，源表和目标表的各个字段的映射等都保存在配置文件中。
配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<TableMaps>
<!--
源表字段为DATE类型时，SqlText用 TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ 替换
源表字段为BIT类型时，SqlText用 TO_NUMBER(TJBS) TJBS 替换
目标字段名称大小写必须和数据库一致
-->
<!--
MainTable为主表，一个配置文件必须且只能一个主表
-->
<!--单位基本情况（主表）-->
<MainTable SrcTableName="T_GBGL_DWJBXX" SrcKey="ID" DestTableName="B000" DestKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B000_Atime" ColumnName="最后操作时间" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B000_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="DWMC" SrcColumn="DWMC" DestColumn="B000_B0101A" ColumnName="单位名称" />
<ColumnMapping SqlText="DWMCLB" SrcColumn="DWMCLB" DestColumn="B000_B0101B" ColumnName="单位名称类别(1041)" />
<ColumnMapping SqlText="DWMCLBMC" SrcColumn="DWMCLBMC" DestColumn="B000_B0101B_show" ColumnName="单位名称类别名称" />
<ColumnMapping SqlText="NAME" SrcColumn="NAME" DestColumn="B000_B0104" ColumnName="单位简称" />
<ColumnMapping SqlText="DWSX" SrcColumn="DWSX" DestColumn="B000_B0107" ColumnName="单位缩名" />
<ColumnMapping SqlText="DWDM" SrcColumn="DWDM" DestColumn="B000_B0111" ColumnName="单位代码(00A2)" />
<ColumnMapping SqlText="DWDMMC" SrcColumn="DWDMMC" DestColumn="B000_B0111_show" ColumnName="单位代码名称" />
<ColumnMapping SqlText="CODE" SrcColumn="CODE" DestColumn="B000_B0114" ColumnName="单位编码" />
<ColumnMapping SqlText="DWSZZQ" SrcColumn="DWSZZQ" DestColumn="B000_B0117" ColumnName="单位所在政区(9001)" />
<ColumnMapping SqlText="DWSZZQMC" SrcColumn="DWSZZQMC" DestColumn="B000_B0117_show" ColumnName="单位所在政区名称" />
<ColumnMapping SqlText="DWSZZQDLB" SrcColumn="DWSZZQDLB" DestColumn="B000_B0117_dlb" ColumnName="单位所在政区DLB" />
<ColumnMapping SqlText="GLCCBS" SrcColumn="GLCCBS" DestColumn="B000_B0121" ColumnName="管理层次标识(9013)" />
<ColumnMapping SqlText="GLCCBSMC" SrcColumn="GLCCBSMC" DestColumn="B000_B0121_show" ColumnName="管理层次标识名称" />
<ColumnMapping SqlText="DWLSGX" SrcColumn="DWLSGX" DestColumn="B000_B0124" ColumnName="单位隶属关系(0003)" />
<ColumnMapping SqlText="DWLSGXMC" SrcColumn="DWLSGXMC" DestColumn="B000_B0124_show" ColumnName="单位隶属关系名称" />
<ColumnMapping SqlText="DWJB" SrcColumn="DWJB" DestColumn="B000_B0127" ColumnName="单位级别(1003)" />
<ColumnMapping SqlText="DWJBMC" SrcColumn="DWJBMC" DestColumn="B000_B0127_show" ColumnName="单位级别名称" />
<ColumnMapping SqlText="DWXZLB" SrcColumn="DWXZLB" DestColumn="B000_B0131" ColumnName="单位性质类别(1004)" />
<ColumnMapping SqlText="DWXZLBMC" SrcColumn="DWXZLBMC" DestColumn="B000_B0131_show" ColumnName="单位性质类别名称" />
<ColumnMapping SqlText="DWSSHY" SrcColumn="DWSSHY" DestColumn="B000_B0134" ColumnName="单位所属行业(0020)" />
<ColumnMapping SqlText="DWSSHYMC" SrcColumn="DWSSHYMC" DestColumn="B000_B0134_show" ColumnName="单位所属行业名称" />
<ColumnMapping SqlText="DWGZZN" SrcColumn="DWGZZN" DestColumn="B000_B0137" ColumnName="单位工作职能" />
<ColumnMapping SqlText="DWGLZN" SrcColumn="DWGLZN" DestColumn="B000_B0141" ColumnName="单位管理职能(1063)" />
<ColumnMapping SqlText="DWGLZNMC" SrcColumn="DWGLZNMC" DestColumn="B000_B0141_show" ColumnName="单位管理职能名称" />
<ColumnMapping SqlText="LSDWMC" SrcColumn="LSDWMC" DestColumn="B000_B0144A" ColumnName="隶属单位名称" />
<ColumnMapping SqlText="LSDWDM" SrcColumn="LSDWDM" DestColumn="B000_B0144B" ColumnName="隶属单位代码" />
<ColumnMapping SqlText="YDWGXBS" SrcColumn="YDWGXBS" DestColumn="B000_B0147" ColumnName="与单位关系标识(9014)" />
<ColumnMapping SqlText="YDWGXBSMC" SrcColumn="YDWGXBSMC" DestColumn="B000_B0147_show" ColumnName="与单位关系标识名称" />
<ColumnMapping SqlText="ZGDWMC" SrcColumn="ZGDWMC" DestColumn="B000_B0151A" ColumnName="主管单位名称" />
<ColumnMapping SqlText="ZGDWDM" SrcColumn="ZGDWDM" DestColumn="B000_B0151B" ColumnName="主管单位代码(9414)" />
<ColumnMapping SqlText="ZGDWDMMC" SrcColumn="ZGDWDMMC" DestColumn="B000_B0151B_show" ColumnName="主管单位代码名称" />
<ColumnMapping SqlText="XGDWMC" SrcColumn="XGDWMC" DestColumn="B000_B0154A" ColumnName="协管单位名称" />
<ColumnMapping SqlText="XGDWDM" SrcColumn="XGDWDM" DestColumn="B000_B0154B" ColumnName="协管单位代码(9414)" />
<ColumnMapping SqlText="XGDWDMMC" SrcColumn="XGDWDMMC" DestColumn="B000_B0154B_show" ColumnName="协管单位代码名称" />
<ColumnMapping SqlText="GKDWMC" SrcColumn="GKDWMC" DestColumn="B000_B0157A" ColumnName="归口单位名称" />
<ColumnMapping SqlText="GKDWDM" SrcColumn="GKDWDM" DestColumn="B000_B0157B" ColumnName="归口单位代码(9414)" />
<ColumnMapping SqlText="GKDWDMMC" SrcColumn="GKDWDMMC" DestColumn="B000_B0157B_show" ColumnName="归口单位代码名称" />
<ColumnMapping SqlText="DWFZR" SrcColumn="DWFZR" DestColumn="B000_B0161" ColumnName="单位负责人" />
<ColumnMapping SqlText="TO_CHAR(DWCLPZRQ,'yyyyMMdd') DWCLPZRQ" SrcColumn="DWCLPZRQ" DestColumn="B000_B0164" ColumnName="单位成立批准日期" />
<ColumnMapping SqlText="DWCLPZWH" SrcColumn="DWCLPZWH" DestColumn="B000_B0167" ColumnName="单位成立批准文号" />
<ColumnMapping SqlText="PZJGMC" SrcColumn="PZJGMC" DestColumn="B000_B0171A" ColumnName="批准机关名称" />
<ColumnMapping SqlText="PZJGDM" SrcColumn="PZJGDM" DestColumn="B000_B0171B" ColumnName="批准机关代码(00A2)" />
<ColumnMapping SqlText="PZJGDMMC" SrcColumn="PZJGDMMC" DestColumn="B000_B0171B_show" ColumnName="批准机关代码名称" />
<ColumnMapping SqlText="YZGDYBS" SrcColumn="YZGDYBS" DestColumn="B000_B0174" ColumnName="有中共党员标识(9015)" />
<ColumnMapping SqlText="YZGDYBSMC" SrcColumn="YZGDYBSMC" DestColumn="B000_B0174_show" ColumnName="有中共党员标识名称" />
<ColumnMapping SqlText="YZGZZBS" SrcColumn="YZGZZBS" DestColumn="B000_B0177" ColumnName="有中共组织标识(9016)" />
<ColumnMapping SqlText="YZGZZBSMC" SrcColumn="YZGZZBSMC" DestColumn="B000_B0177_show" ColumnName="有中共组织标识名称" />
<ColumnMapping SqlText="TO_CHAR(DWCXPZRQ,'yyyyMMdd') DWCXPZRQ" SrcColumn="DWCXPZRQ" DestColumn="B000_B0181" ColumnName="单位撤消批准日期" />
<ColumnMapping SqlText="DWCXPZWH" SrcColumn="DWCXPZWH" DestColumn="B000_B0184" ColumnName="单位撤消批准文号" />
<ColumnMapping SqlText="DWCXPZJG" SrcColumn="DWCXPZJG" DestColumn="B000_B0187A" ColumnName="撤消批准机关" />
<ColumnMapping SqlText="DWCXPZJGDM" SrcColumn="DWCXPZJGDM" DestColumn="B000_B0187B" ColumnName="撤消批准机关代码(9414)" />
<ColumnMapping SqlText="DWCXPZJGDMMC" SrcColumn="DWCXPZJGDMMC" DestColumn="B000_B0187B_show" ColumnName="撤消批准机关代码名称" />
<ColumnMapping SqlText="GKGLCS" SrcColumn="GKGLCS" DestColumn="B000_B4000" ColumnName="归口管理处/室(00A2)" />
<ColumnMapping SqlText="GKGLCSMC" SrcColumn="GKGLCSMC" DestColumn="B000_B4000_show" ColumnName="归口管理处/室名称" />
<ColumnMapping SqlText="DWSSXT" SrcColumn="DWSSXT" DestColumn="B000_B5000" ColumnName="单位所属系统(9636)" />
<ColumnMapping SqlText="DWSSXTMC" SrcColumn="DWSSXTMC" DestColumn="B000_B5000_show" ColumnName="单位所属系统名称" />
<ColumnMapping SqlText="BMH" SrcColumn="BMH" DestColumn="B000_BMH" ColumnName="部门号(9413)" />
<ColumnMapping SqlText="BMHMC" SrcColumn="BMHMC" DestColumn="B000_BMH_show" ColumnName="部门号名称" />
<ColumnMapping SqlText="JBBDWPX" SrcColumn="JBBDWPX" DestColumn="B000_DWPX" ColumnName="季报表单位排序" />
<ColumnMapping SqlText="NZHMCPX" SrcColumn="NZHMCPX" DestColumn="B000_FLBZ" ColumnName="年终花名册排序" />
<ColumnMapping SqlText="FLBZ3" SrcColumn="FLBZ3" DestColumn="B000_FLBZ3" ColumnName="分类标志3" />
<ColumnMapping SqlText="NZHMCDWM" SrcColumn="NZHMCDWM" DestColumn="B000_MLDWM" ColumnName="年终花名册单位名" />
<ColumnMapping SqlText="JBBDWM" SrcColumn="JBBDWM" DestColumn="B000_ODWM" ColumnName="季报表单位名" />
<ColumnMapping SqlText="PCODE" SrcColumn="PCODE" DestColumn="B000_S0114" ColumnName="上层单位代码" />
<ColumnMapping SqlText="BGBZ" SrcColumn="BGBZ" DestColumn="B000_S0190" ColumnName="部管标志(8002)" />
<ColumnMapping SqlText="BGBZMC" SrcColumn="BGBZMC" DestColumn="B000_S0190_show" ColumnName="部管标志名称" />
<ColumnMapping SqlText="WBBZ" SrcColumn="WBBZ" DestColumn="B000_S0191" ColumnName="委办标志(8001)" />
<ColumnMapping SqlText="WBBZMC" SrcColumn="WBBZMC" DestColumn="B000_S0191_show" ColumnName="委办标志名称" />
<ColumnMapping SqlText="GLBZ" SrcColumn="GLBZ" DestColumn="B000_S0192" ColumnName="管理标志" />
<ColumnMapping SqlText="PXXM" SrcColumn="PXXM" DestColumn="B000_S0999" ColumnName="排序项目" />
<ColumnMapping SqlText="HBGBGKDW" SrcColumn="HBGBGKDW" DestColumn="B000_T0101" ColumnName="后备干部归口单位(9414)" />
<ColumnMapping SqlText="HBGBGKDWMC" SrcColumn="HBGBGKDWMC" DestColumn="B000_T0101_show" ColumnName="后备干部归口单位名称" />
<ColumnMapping SqlText="XGBZ" SrcColumn="XGBZ" DestColumn="B000_T0154" ColumnName="协管标志" />
<ColumnMapping SqlText="DWBMXTJ" SrcColumn="DWBMXTJ" DestColumn="B000_unitname" ColumnName="单位部门(系统级)(00A2)" />
<ColumnMapping SqlText="DWBMXTJMC" SrcColumn="DWBMXTJMC" DestColumn="B000_unitname_show" ColumnName="单位部门名称" />
<ColumnMapping SqlText="DWGLLB" SrcColumn="DWGLLB" DestColumn="B000_U4000" ColumnName="单位管理类别(dwgl)" />
<ColumnMapping SqlText="DWGLLBMC" SrcColumn="DWGLLBMC" DestColumn="B000_U4000_Show" ColumnName="单位管理类别名称" />
</MainTable>
<!--
SubTable为子表，SrcForeignKey为和源子表和源主表关联的字段，DestForeignKey为目标子表和目标主表关联的字段
-->
<!--单位编制情况-->
<SubTable SrcTableName="T_GBGL_DWBZQKXX" SrcForeignKey="DWJBXXID" DestTableName="B001" DestForeignKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(BZPZRQ,'yyyyMMdd') BZPZRQ" SrcColumn="BZPZRQ" DestColumn="B001_B0201" ColumnName="编制批准日期" />
<ColumnMapping SqlText="BZPZWH" SrcColumn="BZPZWH" DestColumn="B001_B0204" ColumnName="编制批准文号" />
<ColumnMapping SqlText="PZJGMC" SrcColumn="PZJGMC" DestColumn="B001_B0207A" ColumnName="批准机关名称" />
<ColumnMapping SqlText="PZJGDM" SrcColumn="PZJGDM" DestColumn="B001_B0207B" ColumnName="批准机关代码(00A2)" />
<ColumnMapping SqlText="PZJGDMMC" SrcColumn="PZJGDMMC" DestColumn="B001_B0207B_show" ColumnName="批准机关代码名称" />
<ColumnMapping SqlText="NSJGS" SrcColumn="NSJGS" DestColumn="B001_B0211" ColumnName="内设机构数" />
<ColumnMapping SqlText="NSJGMC" SrcColumn="NSJGMC" DestColumn="B001_B0214" ColumnName="内设机构名称" />
<ColumnMapping SqlText="NSJGJB" SrcColumn="NSJGJB" DestColumn="B001_B0217" ColumnName="内设机构级别(1003)" />
<ColumnMapping SqlText="NSJGJBMC" SrcColumn="NSJGJBMC" DestColumn="B001_B0217_show" ColumnName="内设机构级别名称" />
<ColumnMapping SqlText="BZXZ" SrcColumn="BZXZ" DestColumn="B001_B0221" ColumnName="编制性质(1061)" />
<ColumnMapping SqlText="BZXZMC" SrcColumn="BZXZMC" DestColumn="B001_B0221_show" ColumnName="编制性质名称" />
<ColumnMapping SqlText="PZBZZS" SrcColumn="PZBZZS" DestColumn="B001_B0224" ColumnName="批准编制总数" />
<ColumnMapping SqlText="XZBZS" SrcColumn="XZBZS" DestColumn="B001_B0227" ColumnName="行政编制数" />
<ColumnMapping SqlText="SYBZS" SrcColumn="SYBZS" DestColumn="B001_B0231" ColumnName="事业编制数" />
<ColumnMapping SqlText="QYBZS" SrcColumn="QYBZS" DestColumn="B001_B0234" ColumnName="企业编制数" />
<ColumnMapping SqlText="STBZS" SrcColumn="STBZS" DestColumn="B001_B0237" ColumnName="社团编制数" />
<ColumnMapping SqlText="DWLDZS" SrcColumn="DWLDZS" DestColumn="B001_B0241" ColumnName="单位领导职数" />
<ColumnMapping SqlText="TJFLDZS" SrcColumn="TJFLDZS" DestColumn="B001_B0244" ColumnName="同级非领导职数" />
<ColumnMapping SqlText="YJLDZS" SrcColumn="YJLDZS" DestColumn="B001_B0247" ColumnName="一级领导职数" />
<ColumnMapping SqlText="YJFLDZS" SrcColumn="YJFLDZS" DestColumn="B001_B0251" ColumnName="一级非领导职数" />
<ColumnMapping SqlText="YWRYBZS" SrcColumn="YWRYBZS" DestColumn="B001_B0254" ColumnName="业务人员编制数" />
<ColumnMapping SqlText="GQRYBZS" SrcColumn="GQRYBZS" DestColumn="B001_B0257" ColumnName="工勤人员编制数" />
<ColumnMapping SqlText="YQEBKRS" SrcColumn="YQEBKRS" DestColumn="B001_B0261" ColumnName="用全额拨款人数" />
<ColumnMapping SqlText="YCEBKRS" SrcColumn="YCEBKRS" DestColumn="B001_B0264" ColumnName="用差额拨款人数" />
<ColumnMapping SqlText="YZCZJRS" SrcColumn="YZCZJRS" DestColumn="B001_B0267" ColumnName="用自筹资金人数" />
<ColumnMapping SqlText="BMBH" SrcColumn="BMBH" DestColumn="B001_B4000" ColumnName="部门编号" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B001_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B001_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B001_Atime" ColumnName="最后操作时间" />
</SubTable>
<!--单位通信信息-->
<SubTable SrcTableName="T_GBGL_DWTXXX" SrcForeignKey="DWJBXXID" DestTableName="B003" DestForeignKey="unitcode">
<ColumnMapping SqlText="DWYZBM" SrcColumn="DWYZBM" DestColumn="B003_B0401" ColumnName="单位邮政编码" />
<ColumnMapping SqlText="DWDZ" SrcColumn="DWDZ" DestColumn="B003_B0404" ColumnName="单位地址" />
<ColumnMapping SqlText="DWDHHM" SrcColumn="DWDHHM" DestColumn="B003_B0407" ColumnName="单位电话号码" />
<ColumnMapping SqlText="DWCZHM" SrcColumn="DWCZHM" DestColumn="B003_B0411" ColumnName="单位传真号码" />
<ColumnMapping SqlText="DWDBH" SrcColumn="DWDBH" DestColumn="B003_B0414" ColumnName="单位电报号" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B003_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B003_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B003_Atime" ColumnName="最后操作时间" />
</SubTable>
<!--领导班子届次信息-->
<SubTable SrcTableName="T_GBGL_DWLDBZJCXX" SrcForeignKey="DWJBXXID" DestTableName="B010" DestForeignKey="unitcode">
<ColumnMapping SqlText="LDJTJC" SrcColumn="LDJTJC" DestColumn="B010_D0301" ColumnName="领导集体届次" />
<ColumnMapping SqlText="TO_CHAR(LDJTDXRQ,'yyyyMMdd') LDJTDXRQ" SrcColumn="LDJTDXRQ" DestColumn="B010_D0304" ColumnName="领导集体当选日期" />
<ColumnMapping SqlText="LDJTCYS" SrcColumn="LDJTCYS" DestColumn="B010_D0307" ColumnName="领导集体成员数" />
<ColumnMapping SqlText="JTCYMD" SrcColumn="JTCYMD" DestColumn="B010_D0311" ColumnName="集体成员名单" />
<ColumnMapping SqlText="JTHBCYS" SrcColumn="JTHBCYS" DestColumn="B010_D0314" ColumnName="集体候补成员数" />
<ColumnMapping SqlText="HBCYMD" SrcColumn="HBCYMD" DestColumn="B010_D0317" ColumnName="候补成员名单" />
<ColumnMapping SqlText="CWWYHXRS" SrcColumn="CWWYHXRS" DestColumn="B010_D0321" ColumnName="常委委员候选人数" />
<ColumnMapping SqlText="WYHXRMD" SrcColumn="WYHXRMD" DestColumn="B010_D0324" ColumnName="委员候选人名单" />
<ColumnMapping SqlText="TO_CHAR(SJPFRQ,'yyyyMMdd') SJPFRQ" SrcColumn="SJPFRQ" DestColumn="B010_D0327" ColumnName="上级批复日期" />
<ColumnMapping SqlText="SJPFWH" SrcColumn="SJPFWH" DestColumn="B010_D0331" ColumnName="上级批复文号" />
<ColumnMapping SqlText="XJFS" SrcColumn="XJFS" DestColumn="B010_D0334" ColumnName="选举方式(9027)" />
<ColumnMapping SqlText="XJFSMC" SrcColumn="XJFSMC" DestColumn="B010_D0334_show" ColumnName="选举方式名称" />
<ColumnMapping SqlText="TO_CHAR(CWWYDXRQ,'yyyyMMdd') CWWYDXRQ" SrcColumn="CWWYDXRQ" DestColumn="B010_D0337" ColumnName="常委委员当选日期" />
<ColumnMapping SqlText="DXCWWYS" SrcColumn="DXCWWYS" DestColumn="B010_D0341" ColumnName="当选常委委员数" />
<ColumnMapping SqlText="DXWYMC" SrcColumn="DXWYMC" DestColumn="B010_D0344" ColumnName="当选委员名单" />
<ColumnMapping SqlText="DXSJXM" SrcColumn="DXSJXM" DestColumn="B010_D0347" ColumnName="当选书记姓名" />
<ColumnMapping SqlText="DXFSJS" SrcColumn="DXFSJS" DestColumn="B010_D0351" ColumnName="当选副书记数" />
<ColumnMapping SqlText="DXFSJMD" SrcColumn="DXFSJMD" DestColumn="B010_D0354" ColumnName="当选副书记名单" />
<ColumnMapping SqlText="FSJZS" SrcColumn="FSJZS" DestColumn="B010_D0357" ColumnName="副书记职数" />
<ColumnMapping SqlText="CWZS" SrcColumn="CWZS" DestColumn="B010_D0361" ColumnName="常委职数" />
<ColumnMapping SqlText="ZFZZZRYS" SrcColumn="ZFZZZRYS" DestColumn="B010_D0364" ColumnName="正副职专职人员数" />
<ColumnMapping SqlText="ZZRYMD" SrcColumn="ZZRYMD" DestColumn="B010_D0367" ColumnName="专职人员名单" />
<ColumnMapping SqlText="TO_CHAR(GJJMRQ,'yyyyMMdd') GJJMRQ" SrcColumn="GJJMRQ" DestColumn="B010_D0371" ColumnName="该届届满日期" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B010_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B010_Atime" ColumnName="最后操作时间" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B010_Auser" ColumnName="最后操作者" />
</SubTable>
<!--领导班子变动信息-->
<SubTable SrcTableName="T_GBGL_DWLDBZBDXX" SrcForeignKey="DWJBXXID" DestTableName="B011" DestForeignKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(BDRQ,'yyyyMMdd') BDRQ" SrcColumn="BDRQ" DestColumn="B011_D0401" ColumnName="变动日期" />
<ColumnMapping SqlText="LDBZCYBD" SrcColumn="LDBZCYBD" DestColumn="B011_D0404" ColumnName="领导班子成员变动" />
<ColumnMapping SqlText="DQBZMD" SrcColumn="DQBZMD" DestColumn="B011_D0407" ColumnName="当前班子名单" />
<ColumnMapping SqlText="BDSM" SrcColumn="BDSM" DestColumn="B011_D0411" ColumnName="变动说明" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B011_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B011_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B011_Atime" ColumnName="最后操作时间" />
</SubTable>
</TableMaps>
</configuration>
<configuration>
<TableMaps>
<!--
源表字段为DATE类型时，SqlText用 TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ 替换
源表字段为BIT类型时，SqlText用 TO_NUMBER(TJBS) TJBS 替换
目标字段名称大小写必须和数据库一致
-->
<!--
MainTable为主表，一个配置文件必须且只能一个主表
-->
<!--单位基本情况（主表）-->
<MainTable SrcTableName="T_GBGL_DWJBXX" SrcKey="ID" DestTableName="B000" DestKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B000_Atime" ColumnName="最后操作时间" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B000_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="DWMC" SrcColumn="DWMC" DestColumn="B000_B0101A" ColumnName="单位名称" />
<ColumnMapping SqlText="DWMCLB" SrcColumn="DWMCLB" DestColumn="B000_B0101B" ColumnName="单位名称类别(1041)" />
<ColumnMapping SqlText="DWMCLBMC" SrcColumn="DWMCLBMC" DestColumn="B000_B0101B_show" ColumnName="单位名称类别名称" />
<ColumnMapping SqlText="NAME" SrcColumn="NAME" DestColumn="B000_B0104" ColumnName="单位简称" />
<ColumnMapping SqlText="DWSX" SrcColumn="DWSX" DestColumn="B000_B0107" ColumnName="单位缩名" />
<ColumnMapping SqlText="DWDM" SrcColumn="DWDM" DestColumn="B000_B0111" ColumnName="单位代码(00A2)" />
<ColumnMapping SqlText="DWDMMC" SrcColumn="DWDMMC" DestColumn="B000_B0111_show" ColumnName="单位代码名称" />
<ColumnMapping SqlText="CODE" SrcColumn="CODE" DestColumn="B000_B0114" ColumnName="单位编码" />
<ColumnMapping SqlText="DWSZZQ" SrcColumn="DWSZZQ" DestColumn="B000_B0117" ColumnName="单位所在政区(9001)" />
<ColumnMapping SqlText="DWSZZQMC" SrcColumn="DWSZZQMC" DestColumn="B000_B0117_show" ColumnName="单位所在政区名称" />
<ColumnMapping SqlText="DWSZZQDLB" SrcColumn="DWSZZQDLB" DestColumn="B000_B0117_dlb" ColumnName="单位所在政区DLB" />
<ColumnMapping SqlText="GLCCBS" SrcColumn="GLCCBS" DestColumn="B000_B0121" ColumnName="管理层次标识(9013)" />
<ColumnMapping SqlText="GLCCBSMC" SrcColumn="GLCCBSMC" DestColumn="B000_B0121_show" ColumnName="管理层次标识名称" />
<ColumnMapping SqlText="DWLSGX" SrcColumn="DWLSGX" DestColumn="B000_B0124" ColumnName="单位隶属关系(0003)" />
<ColumnMapping SqlText="DWLSGXMC" SrcColumn="DWLSGXMC" DestColumn="B000_B0124_show" ColumnName="单位隶属关系名称" />
<ColumnMapping SqlText="DWJB" SrcColumn="DWJB" DestColumn="B000_B0127" ColumnName="单位级别(1003)" />
<ColumnMapping SqlText="DWJBMC" SrcColumn="DWJBMC" DestColumn="B000_B0127_show" ColumnName="单位级别名称" />
<ColumnMapping SqlText="DWXZLB" SrcColumn="DWXZLB" DestColumn="B000_B0131" ColumnName="单位性质类别(1004)" />
<ColumnMapping SqlText="DWXZLBMC" SrcColumn="DWXZLBMC" DestColumn="B000_B0131_show" ColumnName="单位性质类别名称" />
<ColumnMapping SqlText="DWSSHY" SrcColumn="DWSSHY" DestColumn="B000_B0134" ColumnName="单位所属行业(0020)" />
<ColumnMapping SqlText="DWSSHYMC" SrcColumn="DWSSHYMC" DestColumn="B000_B0134_show" ColumnName="单位所属行业名称" />
<ColumnMapping SqlText="DWGZZN" SrcColumn="DWGZZN" DestColumn="B000_B0137" ColumnName="单位工作职能" />
<ColumnMapping SqlText="DWGLZN" SrcColumn="DWGLZN" DestColumn="B000_B0141" ColumnName="单位管理职能(1063)" />
<ColumnMapping SqlText="DWGLZNMC" SrcColumn="DWGLZNMC" DestColumn="B000_B0141_show" ColumnName="单位管理职能名称" />
<ColumnMapping SqlText="LSDWMC" SrcColumn="LSDWMC" DestColumn="B000_B0144A" ColumnName="隶属单位名称" />
<ColumnMapping SqlText="LSDWDM" SrcColumn="LSDWDM" DestColumn="B000_B0144B" ColumnName="隶属单位代码" />
<ColumnMapping SqlText="YDWGXBS" SrcColumn="YDWGXBS" DestColumn="B000_B0147" ColumnName="与单位关系标识(9014)" />
<ColumnMapping SqlText="YDWGXBSMC" SrcColumn="YDWGXBSMC" DestColumn="B000_B0147_show" ColumnName="与单位关系标识名称" />
<ColumnMapping SqlText="ZGDWMC" SrcColumn="ZGDWMC" DestColumn="B000_B0151A" ColumnName="主管单位名称" />
<ColumnMapping SqlText="ZGDWDM" SrcColumn="ZGDWDM" DestColumn="B000_B0151B" ColumnName="主管单位代码(9414)" />
<ColumnMapping SqlText="ZGDWDMMC" SrcColumn="ZGDWDMMC" DestColumn="B000_B0151B_show" ColumnName="主管单位代码名称" />
<ColumnMapping SqlText="XGDWMC" SrcColumn="XGDWMC" DestColumn="B000_B0154A" ColumnName="协管单位名称" />
<ColumnMapping SqlText="XGDWDM" SrcColumn="XGDWDM" DestColumn="B000_B0154B" ColumnName="协管单位代码(9414)" />
<ColumnMapping SqlText="XGDWDMMC" SrcColumn="XGDWDMMC" DestColumn="B000_B0154B_show" ColumnName="协管单位代码名称" />
<ColumnMapping SqlText="GKDWMC" SrcColumn="GKDWMC" DestColumn="B000_B0157A" ColumnName="归口单位名称" />
<ColumnMapping SqlText="GKDWDM" SrcColumn="GKDWDM" DestColumn="B000_B0157B" ColumnName="归口单位代码(9414)" />
<ColumnMapping SqlText="GKDWDMMC" SrcColumn="GKDWDMMC" DestColumn="B000_B0157B_show" ColumnName="归口单位代码名称" />
<ColumnMapping SqlText="DWFZR" SrcColumn="DWFZR" DestColumn="B000_B0161" ColumnName="单位负责人" />
<ColumnMapping SqlText="TO_CHAR(DWCLPZRQ,'yyyyMMdd') DWCLPZRQ" SrcColumn="DWCLPZRQ" DestColumn="B000_B0164" ColumnName="单位成立批准日期" />
<ColumnMapping SqlText="DWCLPZWH" SrcColumn="DWCLPZWH" DestColumn="B000_B0167" ColumnName="单位成立批准文号" />
<ColumnMapping SqlText="PZJGMC" SrcColumn="PZJGMC" DestColumn="B000_B0171A" ColumnName="批准机关名称" />
<ColumnMapping SqlText="PZJGDM" SrcColumn="PZJGDM" DestColumn="B000_B0171B" ColumnName="批准机关代码(00A2)" />
<ColumnMapping SqlText="PZJGDMMC" SrcColumn="PZJGDMMC" DestColumn="B000_B0171B_show" ColumnName="批准机关代码名称" />
<ColumnMapping SqlText="YZGDYBS" SrcColumn="YZGDYBS" DestColumn="B000_B0174" ColumnName="有中共党员标识(9015)" />
<ColumnMapping SqlText="YZGDYBSMC" SrcColumn="YZGDYBSMC" DestColumn="B000_B0174_show" ColumnName="有中共党员标识名称" />
<ColumnMapping SqlText="YZGZZBS" SrcColumn="YZGZZBS" DestColumn="B000_B0177" ColumnName="有中共组织标识(9016)" />
<ColumnMapping SqlText="YZGZZBSMC" SrcColumn="YZGZZBSMC" DestColumn="B000_B0177_show" ColumnName="有中共组织标识名称" />
<ColumnMapping SqlText="TO_CHAR(DWCXPZRQ,'yyyyMMdd') DWCXPZRQ" SrcColumn="DWCXPZRQ" DestColumn="B000_B0181" ColumnName="单位撤消批准日期" />
<ColumnMapping SqlText="DWCXPZWH" SrcColumn="DWCXPZWH" DestColumn="B000_B0184" ColumnName="单位撤消批准文号" />
<ColumnMapping SqlText="DWCXPZJG" SrcColumn="DWCXPZJG" DestColumn="B000_B0187A" ColumnName="撤消批准机关" />
<ColumnMapping SqlText="DWCXPZJGDM" SrcColumn="DWCXPZJGDM" DestColumn="B000_B0187B" ColumnName="撤消批准机关代码(9414)" />
<ColumnMapping SqlText="DWCXPZJGDMMC" SrcColumn="DWCXPZJGDMMC" DestColumn="B000_B0187B_show" ColumnName="撤消批准机关代码名称" />
<ColumnMapping SqlText="GKGLCS" SrcColumn="GKGLCS" DestColumn="B000_B4000" ColumnName="归口管理处/室(00A2)" />
<ColumnMapping SqlText="GKGLCSMC" SrcColumn="GKGLCSMC" DestColumn="B000_B4000_show" ColumnName="归口管理处/室名称" />
<ColumnMapping SqlText="DWSSXT" SrcColumn="DWSSXT" DestColumn="B000_B5000" ColumnName="单位所属系统(9636)" />
<ColumnMapping SqlText="DWSSXTMC" SrcColumn="DWSSXTMC" DestColumn="B000_B5000_show" ColumnName="单位所属系统名称" />
<ColumnMapping SqlText="BMH" SrcColumn="BMH" DestColumn="B000_BMH" ColumnName="部门号(9413)" />
<ColumnMapping SqlText="BMHMC" SrcColumn="BMHMC" DestColumn="B000_BMH_show" ColumnName="部门号名称" />
<ColumnMapping SqlText="JBBDWPX" SrcColumn="JBBDWPX" DestColumn="B000_DWPX" ColumnName="季报表单位排序" />
<ColumnMapping SqlText="NZHMCPX" SrcColumn="NZHMCPX" DestColumn="B000_FLBZ" ColumnName="年终花名册排序" />
<ColumnMapping SqlText="FLBZ3" SrcColumn="FLBZ3" DestColumn="B000_FLBZ3" ColumnName="分类标志3" />
<ColumnMapping SqlText="NZHMCDWM" SrcColumn="NZHMCDWM" DestColumn="B000_MLDWM" ColumnName="年终花名册单位名" />
<ColumnMapping SqlText="JBBDWM" SrcColumn="JBBDWM" DestColumn="B000_ODWM" ColumnName="季报表单位名" />
<ColumnMapping SqlText="PCODE" SrcColumn="PCODE" DestColumn="B000_S0114" ColumnName="上层单位代码" />
<ColumnMapping SqlText="BGBZ" SrcColumn="BGBZ" DestColumn="B000_S0190" ColumnName="部管标志(8002)" />
<ColumnMapping SqlText="BGBZMC" SrcColumn="BGBZMC" DestColumn="B000_S0190_show" ColumnName="部管标志名称" />
<ColumnMapping SqlText="WBBZ" SrcColumn="WBBZ" DestColumn="B000_S0191" ColumnName="委办标志(8001)" />
<ColumnMapping SqlText="WBBZMC" SrcColumn="WBBZMC" DestColumn="B000_S0191_show" ColumnName="委办标志名称" />
<ColumnMapping SqlText="GLBZ" SrcColumn="GLBZ" DestColumn="B000_S0192" ColumnName="管理标志" />
<ColumnMapping SqlText="PXXM" SrcColumn="PXXM" DestColumn="B000_S0999" ColumnName="排序项目" />
<ColumnMapping SqlText="HBGBGKDW" SrcColumn="HBGBGKDW" DestColumn="B000_T0101" ColumnName="后备干部归口单位(9414)" />
<ColumnMapping SqlText="HBGBGKDWMC" SrcColumn="HBGBGKDWMC" DestColumn="B000_T0101_show" ColumnName="后备干部归口单位名称" />
<ColumnMapping SqlText="XGBZ" SrcColumn="XGBZ" DestColumn="B000_T0154" ColumnName="协管标志" />
<ColumnMapping SqlText="DWBMXTJ" SrcColumn="DWBMXTJ" DestColumn="B000_unitname" ColumnName="单位部门(系统级)(00A2)" />
<ColumnMapping SqlText="DWBMXTJMC" SrcColumn="DWBMXTJMC" DestColumn="B000_unitname_show" ColumnName="单位部门名称" />
<ColumnMapping SqlText="DWGLLB" SrcColumn="DWGLLB" DestColumn="B000_U4000" ColumnName="单位管理类别(dwgl)" />
<ColumnMapping SqlText="DWGLLBMC" SrcColumn="DWGLLBMC" DestColumn="B000_U4000_Show" ColumnName="单位管理类别名称" />
</MainTable>
<!--
SubTable为子表，SrcForeignKey为和源子表和源主表关联的字段，DestForeignKey为目标子表和目标主表关联的字段
-->
<!--单位编制情况-->
<SubTable SrcTableName="T_GBGL_DWBZQKXX" SrcForeignKey="DWJBXXID" DestTableName="B001" DestForeignKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(BZPZRQ,'yyyyMMdd') BZPZRQ" SrcColumn="BZPZRQ" DestColumn="B001_B0201" ColumnName="编制批准日期" />
<ColumnMapping SqlText="BZPZWH" SrcColumn="BZPZWH" DestColumn="B001_B0204" ColumnName="编制批准文号" />
<ColumnMapping SqlText="PZJGMC" SrcColumn="PZJGMC" DestColumn="B001_B0207A" ColumnName="批准机关名称" />
<ColumnMapping SqlText="PZJGDM" SrcColumn="PZJGDM" DestColumn="B001_B0207B" ColumnName="批准机关代码(00A2)" />
<ColumnMapping SqlText="PZJGDMMC" SrcColumn="PZJGDMMC" DestColumn="B001_B0207B_show" ColumnName="批准机关代码名称" />
<ColumnMapping SqlText="NSJGS" SrcColumn="NSJGS" DestColumn="B001_B0211" ColumnName="内设机构数" />
<ColumnMapping SqlText="NSJGMC" SrcColumn="NSJGMC" DestColumn="B001_B0214" ColumnName="内设机构名称" />
<ColumnMapping SqlText="NSJGJB" SrcColumn="NSJGJB" DestColumn="B001_B0217" ColumnName="内设机构级别(1003)" />
<ColumnMapping SqlText="NSJGJBMC" SrcColumn="NSJGJBMC" DestColumn="B001_B0217_show" ColumnName="内设机构级别名称" />
<ColumnMapping SqlText="BZXZ" SrcColumn="BZXZ" DestColumn="B001_B0221" ColumnName="编制性质(1061)" />
<ColumnMapping SqlText="BZXZMC" SrcColumn="BZXZMC" DestColumn="B001_B0221_show" ColumnName="编制性质名称" />
<ColumnMapping SqlText="PZBZZS" SrcColumn="PZBZZS" DestColumn="B001_B0224" ColumnName="批准编制总数" />
<ColumnMapping SqlText="XZBZS" SrcColumn="XZBZS" DestColumn="B001_B0227" ColumnName="行政编制数" />
<ColumnMapping SqlText="SYBZS" SrcColumn="SYBZS" DestColumn="B001_B0231" ColumnName="事业编制数" />
<ColumnMapping SqlText="QYBZS" SrcColumn="QYBZS" DestColumn="B001_B0234" ColumnName="企业编制数" />
<ColumnMapping SqlText="STBZS" SrcColumn="STBZS" DestColumn="B001_B0237" ColumnName="社团编制数" />
<ColumnMapping SqlText="DWLDZS" SrcColumn="DWLDZS" DestColumn="B001_B0241" ColumnName="单位领导职数" />
<ColumnMapping SqlText="TJFLDZS" SrcColumn="TJFLDZS" DestColumn="B001_B0244" ColumnName="同级非领导职数" />
<ColumnMapping SqlText="YJLDZS" SrcColumn="YJLDZS" DestColumn="B001_B0247" ColumnName="一级领导职数" />
<ColumnMapping SqlText="YJFLDZS" SrcColumn="YJFLDZS" DestColumn="B001_B0251" ColumnName="一级非领导职数" />
<ColumnMapping SqlText="YWRYBZS" SrcColumn="YWRYBZS" DestColumn="B001_B0254" ColumnName="业务人员编制数" />
<ColumnMapping SqlText="GQRYBZS" SrcColumn="GQRYBZS" DestColumn="B001_B0257" ColumnName="工勤人员编制数" />
<ColumnMapping SqlText="YQEBKRS" SrcColumn="YQEBKRS" DestColumn="B001_B0261" ColumnName="用全额拨款人数" />
<ColumnMapping SqlText="YCEBKRS" SrcColumn="YCEBKRS" DestColumn="B001_B0264" ColumnName="用差额拨款人数" />
<ColumnMapping SqlText="YZCZJRS" SrcColumn="YZCZJRS" DestColumn="B001_B0267" ColumnName="用自筹资金人数" />
<ColumnMapping SqlText="BMBH" SrcColumn="BMBH" DestColumn="B001_B4000" ColumnName="部门编号" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B001_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B001_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B001_Atime" ColumnName="最后操作时间" />
</SubTable>
<!--单位通信信息-->
<SubTable SrcTableName="T_GBGL_DWTXXX" SrcForeignKey="DWJBXXID" DestTableName="B003" DestForeignKey="unitcode">
<ColumnMapping SqlText="DWYZBM" SrcColumn="DWYZBM" DestColumn="B003_B0401" ColumnName="单位邮政编码" />
<ColumnMapping SqlText="DWDZ" SrcColumn="DWDZ" DestColumn="B003_B0404" ColumnName="单位地址" />
<ColumnMapping SqlText="DWDHHM" SrcColumn="DWDHHM" DestColumn="B003_B0407" ColumnName="单位电话号码" />
<ColumnMapping SqlText="DWCZHM" SrcColumn="DWCZHM" DestColumn="B003_B0411" ColumnName="单位传真号码" />
<ColumnMapping SqlText="DWDBH" SrcColumn="DWDBH" DestColumn="B003_B0414" ColumnName="单位电报号" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B003_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B003_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B003_Atime" ColumnName="最后操作时间" />
</SubTable>
<!--领导班子届次信息-->
<SubTable SrcTableName="T_GBGL_DWLDBZJCXX" SrcForeignKey="DWJBXXID" DestTableName="B010" DestForeignKey="unitcode">
<ColumnMapping SqlText="LDJTJC" SrcColumn="LDJTJC" DestColumn="B010_D0301" ColumnName="领导集体届次" />
<ColumnMapping SqlText="TO_CHAR(LDJTDXRQ,'yyyyMMdd') LDJTDXRQ" SrcColumn="LDJTDXRQ" DestColumn="B010_D0304" ColumnName="领导集体当选日期" />
<ColumnMapping SqlText="LDJTCYS" SrcColumn="LDJTCYS" DestColumn="B010_D0307" ColumnName="领导集体成员数" />
<ColumnMapping SqlText="JTCYMD" SrcColumn="JTCYMD" DestColumn="B010_D0311" ColumnName="集体成员名单" />
<ColumnMapping SqlText="JTHBCYS" SrcColumn="JTHBCYS" DestColumn="B010_D0314" ColumnName="集体候补成员数" />
<ColumnMapping SqlText="HBCYMD" SrcColumn="HBCYMD" DestColumn="B010_D0317" ColumnName="候补成员名单" />
<ColumnMapping SqlText="CWWYHXRS" SrcColumn="CWWYHXRS" DestColumn="B010_D0321" ColumnName="常委委员候选人数" />
<ColumnMapping SqlText="WYHXRMD" SrcColumn="WYHXRMD" DestColumn="B010_D0324" ColumnName="委员候选人名单" />
<ColumnMapping SqlText="TO_CHAR(SJPFRQ,'yyyyMMdd') SJPFRQ" SrcColumn="SJPFRQ" DestColumn="B010_D0327" ColumnName="上级批复日期" />
<ColumnMapping SqlText="SJPFWH" SrcColumn="SJPFWH" DestColumn="B010_D0331" ColumnName="上级批复文号" />
<ColumnMapping SqlText="XJFS" SrcColumn="XJFS" DestColumn="B010_D0334" ColumnName="选举方式(9027)" />
<ColumnMapping SqlText="XJFSMC" SrcColumn="XJFSMC" DestColumn="B010_D0334_show" ColumnName="选举方式名称" />
<ColumnMapping SqlText="TO_CHAR(CWWYDXRQ,'yyyyMMdd') CWWYDXRQ" SrcColumn="CWWYDXRQ" DestColumn="B010_D0337" ColumnName="常委委员当选日期" />
<ColumnMapping SqlText="DXCWWYS" SrcColumn="DXCWWYS" DestColumn="B010_D0341" ColumnName="当选常委委员数" />
<ColumnMapping SqlText="DXWYMC" SrcColumn="DXWYMC" DestColumn="B010_D0344" ColumnName="当选委员名单" />
<ColumnMapping SqlText="DXSJXM" SrcColumn="DXSJXM" DestColumn="B010_D0347" ColumnName="当选书记姓名" />
<ColumnMapping SqlText="DXFSJS" SrcColumn="DXFSJS" DestColumn="B010_D0351" ColumnName="当选副书记数" />
<ColumnMapping SqlText="DXFSJMD" SrcColumn="DXFSJMD" DestColumn="B010_D0354" ColumnName="当选副书记名单" />
<ColumnMapping SqlText="FSJZS" SrcColumn="FSJZS" DestColumn="B010_D0357" ColumnName="副书记职数" />
<ColumnMapping SqlText="CWZS" SrcColumn="CWZS" DestColumn="B010_D0361" ColumnName="常委职数" />
<ColumnMapping SqlText="ZFZZZRYS" SrcColumn="ZFZZZRYS" DestColumn="B010_D0364" ColumnName="正副职专职人员数" />
<ColumnMapping SqlText="ZZRYMD" SrcColumn="ZZRYMD" DestColumn="B010_D0367" ColumnName="专职人员名单" />
<ColumnMapping SqlText="TO_CHAR(GJJMRQ,'yyyyMMdd') GJJMRQ" SrcColumn="GJJMRQ" DestColumn="B010_D0371" ColumnName="该届届满日期" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B010_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B010_Atime" ColumnName="最后操作时间" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B010_Auser" ColumnName="最后操作者" />
</SubTable>
<!--领导班子变动信息-->
<SubTable SrcTableName="T_GBGL_DWLDBZBDXX" SrcForeignKey="DWJBXXID" DestTableName="B011" DestForeignKey="unitcode">
<ColumnMapping SqlText="TO_CHAR(BDRQ,'yyyyMMdd') BDRQ" SrcColumn="BDRQ" DestColumn="B011_D0401" ColumnName="变动日期" />
<ColumnMapping SqlText="LDBZCYBD" SrcColumn="LDBZCYBD" DestColumn="B011_D0404" ColumnName="领导班子成员变动" />
<ColumnMapping SqlText="DQBZMD" SrcColumn="DQBZMD" DestColumn="B011_D0407" ColumnName="当前班子名单" />
<ColumnMapping SqlText="BDSM" SrcColumn="BDSM" DestColumn="B011_D0411" ColumnName="变动说明" />
<ColumnMapping SqlText="TO_NUMBER(TJBS) TJBS" SrcColumn="TJBS" DestColumn="B011_Acount" ColumnName="统计标识" />
<ColumnMapping SqlText="ZHCZZ" SrcColumn="ZHCZZ" DestColumn="B011_Auser" ColumnName="最后操作者" />
<ColumnMapping SqlText="TO_CHAR(ZHCZSJ,'yyyyMMdd') ZHCZSJ" SrcColumn="ZHCZSJ" DestColumn="B011_Atime" ColumnName="最后操作时间" />
</SubTable>
</TableMaps>
</configuration>
主要源码：
代码
private void buttonBegin_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBoxFile.Text.Trim()))
{
MessageBox.Show("请选择配置文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
try
{
XmlDocument doc = new XmlDocument();
doc.Load(textBoxFile.Text.Trim());
XmlNodeList mainTableNodes = doc.SelectNodes("configuration/TableMaps/MainTable");
DataTable dtSourceMain = new DataTable();
using (OracleConnection conn = new OracleConnection(SourceConnectString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
StringBuilder sbSql = new StringBuilder();
String srcTableName;
String srcKey;
String destTableName;
String destKey;
if (mainTableNodes.Count < 1)
{
MessageBox.Show("配置文件不对，缺少主表！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
srcTableName = mainTableNodes[0].Attributes["SrcTableName"].Value;
srcKey = mainTableNodes[0].Attributes["SrcKey"].Value;
destTableName = mainTableNodes[0].Attributes["DestTableName"].Value;
DeleteTargetTable(destTableName);
destKey = mainTableNodes[0].Attributes["DestKey"].Value;
sbSql.Append("select ");
foreach (XmlNode node in mainTableNodes[0])
sbSql.AppendFormat("{0}, ", node.Attributes["SqlText"].Value);
//sbSql.Remove(sbSql.ToString().LastIndexOf(","), 1);
sbSql.Append(srcKey);
sbSql.AppendFormat(" from {0}", srcTableName);
cmd.CommandText = sbSql.ToString();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dtSourceMain);
dtSourceMain.Columns.Add(destKey, Type.GetType("System.Int32"));
for (int i = 0; i < dtSourceMain.Rows.Count; i++)
{
dtSourceMain.Rows[i][destKey] = i + 1;
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(TargetConnectString, SqlBulkCopyOptions.UseInternalTransaction))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 300;
sqlBC.DestinationTableName = destTableName;
//主表数据插入
sqlBC.ColumnMappings.Add(destKey, destKey);
foreach (XmlNode childNode in mainTableNodes[0].ChildNodes)
{
sqlBC.ColumnMappings.Add(childNode.Attributes["SrcColumn"].Value, childNode.Attributes["DestColumn"].Value);
}
sqlBC.WriteToServer(dtSourceMain);
//准备子表数据
XmlNodeList subTableNodes = doc.SelectNodes("configuration/TableMaps/SubTable");
progressBar.Maximum = subTableNodes.Count;
this.progressBar.Value = 1;
this.progressBar.Step = 1;
this.progressBar.PerformStep();
String srcForeignKey;
String destForeignKey;
foreach (XmlNode node in subTableNodes)
{
this.progressBar.PerformStep();
DataTable dtSourceSub = new DataTable();
srcTableName = node.Attributes["SrcTableName"].Value;
srcForeignKey = node.Attributes["SrcForeignKey"].Value;
destTableName = node.Attributes["DestTableName"].Value;
DeleteTargetTable(destTableName);
destForeignKey = node.Attributes["DestForeignKey"].Value;
sqlBC.DestinationTableName = destTableName;
sqlBC.ColumnMappings.Clear();
sqlBC.ColumnMappings.Add(destForeignKey, destForeignKey);
sbSql = new StringBuilder();
sbSql.Append("select ");
foreach (XmlNode childnodes in node.ChildNodes)
{
sbSql.AppendFormat("{0}, ", childnodes.Attributes["SqlText"].Value);
sqlBC.ColumnMappings.Add(childnodes.Attributes["SrcColumn"].Value, childnodes.Attributes["DestColumn"].Value);
}
sbSql.Append(srcForeignKey);
sbSql.AppendFormat(" from {0}", srcTableName);
cmd.CommandText = sbSql.ToString();
adapter = new OracleDataAdapter(cmd);
adapter.Fill(dtSourceSub);
dtSourceSub.Columns.Add(destForeignKey, Type.GetType("System.Int32"));
for (int i = 0; i < dtSourceSub.Rows.Count; i++)
{
DataRow[] drs = dtSourceMain.Select(String.Format("{0}='{1}'", srcKey, dtSourceSub.Rows[i][srcForeignKey]));
if (drs.Length > 0)
dtSourceSub.Rows[i][destForeignKey] = drs[0][destKey];
}
sqlBC.WriteToServer(dtSourceSub);
}
}
}
MessageBox.Show("数据转换成功，请查看目标表数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
progressBar.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
{
if (String.IsNullOrEmpty(textBoxFile.Text.Trim()))
{
MessageBox.Show("请选择配置文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
try
{
XmlDocument doc = new XmlDocument();
doc.Load(textBoxFile.Text.Trim());
XmlNodeList mainTableNodes = doc.SelectNodes("configuration/TableMaps/MainTable");
DataTable dtSourceMain = new DataTable();
using (OracleConnection conn = new OracleConnection(SourceConnectString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
StringBuilder sbSql = new StringBuilder();
String srcTableName;
String srcKey;
String destTableName;
String destKey;
if (mainTableNodes.Count < 1)
{
MessageBox.Show("配置文件不对，缺少主表！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
srcTableName = mainTableNodes[0].Attributes["SrcTableName"].Value;
srcKey = mainTableNodes[0].Attributes["SrcKey"].Value;
destTableName = mainTableNodes[0].Attributes["DestTableName"].Value;
DeleteTargetTable(destTableName);
destKey = mainTableNodes[0].Attributes["DestKey"].Value;
sbSql.Append("select ");
foreach (XmlNode node in mainTableNodes[0])
sbSql.AppendFormat("{0}, ", node.Attributes["SqlText"].Value);
//sbSql.Remove(sbSql.ToString().LastIndexOf(","), 1);
sbSql.Append(srcKey);
sbSql.AppendFormat(" from {0}", srcTableName);
cmd.CommandText = sbSql.ToString();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dtSourceMain);
dtSourceMain.Columns.Add(destKey, Type.GetType("System.Int32"));
for (int i = 0; i < dtSourceMain.Rows.Count; i++)
{
dtSourceMain.Rows[i][destKey] = i + 1;
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(TargetConnectString, SqlBulkCopyOptions.UseInternalTransaction))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 300;
sqlBC.DestinationTableName = destTableName;
//主表数据插入
sqlBC.ColumnMappings.Add(destKey, destKey);
foreach (XmlNode childNode in mainTableNodes[0].ChildNodes)
{
sqlBC.ColumnMappings.Add(childNode.Attributes["SrcColumn"].Value, childNode.Attributes["DestColumn"].Value);
}
sqlBC.WriteToServer(dtSourceMain);
//准备子表数据
XmlNodeList subTableNodes = doc.SelectNodes("configuration/TableMaps/SubTable");
progressBar.Maximum = subTableNodes.Count;
this.progressBar.Value = 1;
this.progressBar.Step = 1;
this.progressBar.PerformStep();
String srcForeignKey;
String destForeignKey;
foreach (XmlNode node in subTableNodes)
{
this.progressBar.PerformStep();
DataTable dtSourceSub = new DataTable();
srcTableName = node.Attributes["SrcTableName"].Value;
srcForeignKey = node.Attributes["SrcForeignKey"].Value;
destTableName = node.Attributes["DestTableName"].Value;
DeleteTargetTable(destTableName);
destForeignKey = node.Attributes["DestForeignKey"].Value;
sqlBC.DestinationTableName = destTableName;
sqlBC.ColumnMappings.Clear();
sqlBC.ColumnMappings.Add(destForeignKey, destForeignKey);
sbSql = new StringBuilder();
sbSql.Append("select ");
foreach (XmlNode childnodes in node.ChildNodes)
{
sbSql.AppendFormat("{0}, ", childnodes.Attributes["SqlText"].Value);
sqlBC.ColumnMappings.Add(childnodes.Attributes["SrcColumn"].Value, childnodes.Attributes["DestColumn"].Value);
}
sbSql.Append(srcForeignKey);
sbSql.AppendFormat(" from {0}", srcTableName);
cmd.CommandText = sbSql.ToString();
adapter = new OracleDataAdapter(cmd);
adapter.Fill(dtSourceSub);
dtSourceSub.Columns.Add(destForeignKey, Type.GetType("System.Int32"));
for (int i = 0; i < dtSourceSub.Rows.Count; i++)
{
DataRow[] drs = dtSourceMain.Select(String.Format("{0}='{1}'", srcKey, dtSourceSub.Rows[i][srcForeignKey]));
if (drs.Length > 0)
dtSourceSub.Rows[i][destForeignKey] = drs[0][destKey];
}
sqlBC.WriteToServer(dtSourceSub);
}
}
}
MessageBox.Show("数据转换成功，请查看目标表数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
progressBar.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
备注
使用SqlBulkCopy 类，配置映射时，目标表各个字段的名称必须和数据库中的字段名称保持大小写一致。
各个数据类型的转换的时候，必须在映射的DataTable已经转换好，最好是查询的SQL语句，已经转换好，相见配置文件。
插入的时候，目标表不能有重复主键，否则失败；每条记录的每个字段的实际值大小不能超过目标表，否则报错。
