tuzi

用代码从文件中导入数据到SQL Server

引言

山西快乐十分走势 www.yfhdr.cn 导入数据到SQL Server 是常见的需求,特别是定期导入这种需求。

对于定期导入主要有以下几种方式可选择:

  1. Bulk Insert
  2. Bcp Utility
  3. OpenRowSet
  4. 写程序导入(使用poi, npoi, ExcelDataReader 等各种组件导入导出,后面不做描述)

接下来我们详细分析下他们的特点和要求

Bulk Insert

执行环境:SQL Server,属于Transact SQL, 可在查询窗口和存储过程中运行。

支持版本:从SQL Server2008开始支持

语法详见:MSDN Bulk Insert

功能特点:支持txt, csv等有分隔符分割数据的文件,因CSV不支持多个sheet, 所以Bulk Insert只支持导入单个文件。

使用举例:

在SP中使用,数据文件和分隔符是可变的,所以使用下面的方式:

强调几个比较重要的配置:

  • FilePath:对于@p_filePath,如果是远程磁盘,需使用UNC(Universal Naming Convention )格式, \\Systemname或主机IP\ShareName\Path\FileName
  • DataFileType:数据编码{ 'char' | 'native' | 'widechar' | 'widenative' },widechar和widenative支持unicode

在Windows 批处理文件中用 osql 调用使用Bulk Insert 的SP,当然你也可以直接在在Sql Server Job 中调用。

 Bcp Utility 

执行环境:window 命令窗口或在batch文件中执行,有batch文件就可以用window计划或Control-M等工具调用。

语法详见:BCP Utility

功能特点:可以在数据文件与SQL Server之间导入、导出数据,也可以导出格式文件。

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
  • in 导入数据到table
  • out 导出table 数据到文件
  • queryout 导出查询结果到文件
  • format nul 导出格式文件

使用举例:

使用时可能碰到各种报错,例如数据库连接不成功、文件路径不正确、表访问修改权限不够、格式文件不对或数据文件有问题等等

我在使用时采用以下思路:

  • 先用format nul 根据表导出格式文件
  • 使用out 导出数据文件
  • 在导出的数据文件基础上修改,制作新的数据文件
  • 使用in 和导出的格式文件导入数据

下面的命令使用“,”进行分割,可以使用csv文件,详细命令如下:

  • -S:server name
  • -T:SQL Server 信任模式,若不指定,则需用-U、-P指定数据库用户名,密码
  • -t :字段分隔符,默认是tab字符,-t,表示用逗号分隔
  • -c:表数据是字符型,以tab字符为字段分隔符,以\r\n为行分隔符
bcp instance.dbo.table format nul -S servername -f FormatFile.fmt -T -t, -c

bcp instance.dbo.table out c:\dataFile.csv -S servername -T -t, -c

bcp instance.dbo.table in dataFilePath -f formatFilePath -S serverName -T

 对于格式文件,说明如下,图片来至MSDN

 

OpenRowSet

 

执行环境:SQL Server,属于Transact SQL, 可在查询窗口和存储过程中运行。

支持版本:从SQL Server2008开始支持

语法详见:MSDN Bulk Insert

功能特点:支持txt, csv等有分隔符分割数据的文件、excel、代替dblink从其他数据库(oracle 等)查询数据。

Provider String:

  • Jet引擎 :Microsoft.Jet.OLEDB.4.0   
    • Jet 是 Joint Engine Technology
    • "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=databaseName;User ID=MyUserID;Password=MyPassword;"
  • ODBC:MSDASQL
    • "Provider=MSDASQL;DSN=dsnName;UID=MyUserID;PWD=MyPassword;"
  • Oracel:MSDAORA
    • "Provider=MSDAORA;Data Source=serverName;User ID=MyUserID; Password=MyPassword;"

更多请见MSDN参考

使用举例:

下图面是从excel 2007中读取数据的例子,从指定的work sheet 中读取数据:

 

posted @ 2018-07-13 16:18 傍晚雨 阅读(...) 评论(...) 编辑 收藏
  • 溃疡恶变也会腹痛 这些肿瘤最容易被误诊 2018-09-04
  • 候选企业:光大证券股份有限公司 2018-09-04
  • 25年全球健康三大变化 2018-07-26
  • 林肯中心室内乐协会再掀浪潮 完整录音入驻中国 亚洲巡演整装待发 室内乐 厂牌 2018-07-25
  • 社会主义是过渡阶段,最终实现共产主义才是其目的。社会主义是在消灭私有制,建立公有制直至无私,实现共产主义。 2018-07-25
  • 呼死你团伙被摧毁 封停83万余个账号抓获210余人 2018-07-25
  • 回复@老老保老张工:伪高工不仅智力低下,知识也很匮乏也!连啥叫计划经济都没搞懂! 2018-07-24
  • 中国经济充当了世界经济发展的火车头。但是,作为世界经济火车头的中国,在世界主要经济体股市都走牛的情况下,为何熊途漫漫?这种不正常的现象,背后是我们资本市场的投融 2018-07-24
  • 社评:中国打的是对美贸易自卫反击战 2018-07-24
  • 世界献血者日 近距离接触一袋合格血的成长历程 2018-07-23
  • 便道汛期被淹学生上学遇阻 来凤老伯坚持背娃过河40年 2018-07-23
  • 以朝鲜民主主义人民共和国为例,难道特金会是由朝鲜工人、农民主导的?[哈哈] 2018-07-23
  • 设计众议院:神奇的MQB让全新一代宝来重生 2018-07-22
  • 回复@不能这样啊:那你不是可以啥事不做天天甩起手耍?你自己不求上进还不准别人有所追求么? 2018-07-22
  • 三位基层书记的一周:思想不转换 其他都白扯 2018-07-22
  • 494| 713| 980| 769| 312| 578| 718| 514| 641| 834|