Menu

bulk insert在使用中遇到的一些问题

  SQL Server中可以通过bulk insert的方式快速大批量的导入数据。但这种方式在使用上有一些复杂,而且也比较灵活,想要更多的了解可以通过这篇文档。本文不做bulk insert的使用介绍,本文想要说一说自己在使用时遇到的一些问题。
  bulk insert的使用比较简单:

BULK INSERT EOMP.dbo.YOUR-TABLE 
FROM 'C:\your-file.txt' WITH (FORMATFILE='C:\formatfile.xml');

  这里的意思是往EOMP.dbo.YOUR-TABLE1这里面插入数据,数据源为C:\your-file.txt,然后我们需要使用一个具有该表单信息以及该数据源文本格式的信息的一个xml文件。在官方文档中也列举了其他不需要格式文档的使用方式。
  笔者使用的是带格式化文件的方式,这里会首先遇到一个问题。自己是通过一些博文去学习如何使用bulk insert,然后按照上面的方式照做的话是直接出现了错误。因为这些博文会告诉你在提供格式化文件时直接在上面修改对应的字段名之类的信息就可以了,一个xml类型的格式化文件内容大概是这样的:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="32" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="creator" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="createtime" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="org" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="lockstatus" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

  于是我依据博文的方法,去修改在ROW标签下面的字段名以匹配我需要传入数据的表单,但是发现并不可行,他会报各种xml文件的格式错误。而正确的方式,应该是在你的数据库的机器上,使用SQL Server的bcp工具,导出一个文件来,这样这个文件才是符合你本地数据库的正确的xml文件。bcp是一个SQL Server的数据导出导入工具,一般在你安装数据库时就已经存在了并且也写到了环境变量里面,使用bcp工具直接通过命令行运行以下命令即可:

bcp EOMP.dbo.u_table8 format nul -c -x -f d:\Department-c.xml -t, -T

  EOMP.dbo.u_table8是你的数据库 表单,d:\Department-c.xml是生成的xml文件的存放路径,对于其余的那些命令可以通过这里去查询了解。
  上面这个是第一个坑。后面笔者觉得xml类型的格式文件配置比较复杂,程序生成时工作较多。因此,又通过网络了解到了另一种类型的格式文件.fmt。在踩过上面的坑以后,我很自然的去找如何通过bcp工具生成.fmt文件:

bcp EOMP.dbo.u_table8 format nul -c -f d:\Department-n.fmt -T

  文件内容大概是这样的:

10.0
67
1       SQLINT              0       4       ","   1     id                             ""
2       SQLCHAR             2       16      ","   2     creator                        Chinese_PRC_CI_AS
3       SQLCHAR             2       32      ","   3     createtime                     Chinese_PRC_CI_AS
4       SQLCHAR             2       50      ","   4     org                            Chinese_PRC_CI_AS
5       SQLINT              0       4       "\r\n"   5     lockstatus                     ""

  这里可以通过最后一行可以看见,他指定的换行的方式是\r\n的换行符,那么,在你的数据源文件中,每一行的结束部分都要保证有这个换行符。之前笔者就是因为在最后一行未提供换行符一直不能导入成功。
  以上就是笔者踩过的两个坑,总结下来就是格式文件一定要先用本地的数据库去生成一个模板再以此为基础做调整。数据源中的每一行都一定要符合格式文件里关于各字段的分隔符,长度,换行符的要求。最后再提供一个符合.fmt文件格式的数据源文件内容作为参考。

admin,2019-04-12 10:37:02,Org01,1,1
admin,2019-04-12 10:37:02,Org01,1,1
admin,2019-04-12 10:37:02,Org01,1,1
admin,2019-04-12 10:37:02,Org01,1,1
admin,2019-04-12 10:37:02,Org01,1,1
Posted In:
数据库

暂无评论

添加评论 您的邮箱将会被妥善保存