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 傍晚雨 阅读(...) 评论(...) 编辑 收藏
  • 高温“烤验”,品读这些自带凉意的避暑诗词 2019-05-19
  • 2015科教频道中秋晚会《天涯共此时》 2019-05-19
  • 特朗普政府用政治“边缘政策”处理贸易问题是玩火 2019-05-17
  • 高清:探访苟坝见证历史 红军在此留下珍贵遗物 2019-05-15
  • 交通小事故,几分钟就能处理完 2019-05-15
  • 在现时代,无论中国还是西方发达国家都是社会财富公有制和私有制并存的社会,由于仍旧存在社会财富私有制,所以必然存在贫富差别,离开私有制来谈“贫”和“富”... 2019-05-15
  • 【学习时刻·经济实说②】管清友:中央经济工作会议的十大亮点 2019-05-15
  • 绿染江源,千湖归来——三江源生态保护建设取得阶段性成效 2019-05-13
  • 把握和传承好“变则通”思想(大家手笔) 2019-05-13
  • 5月份70个大中城市商品住宅销售情况:一线城市商品房价格同比持续下降 2019-05-08
  • 官方:装备管理失职 恒大俱乐部总经理等人遭处罚 2019-05-01
  • 中共一大代表中最早辞世的王尽美:3首小诗与27年人生 2019-04-29
  • 不管怎么修饰辞藻,只要放弃革命,就是苏联的结果,还用证明吗? 2019-04-27
  • 新和县:12333为参保群众提供咨询便利 2019-04-22
  • 秋冬进补“小人参” 称霸国人餐桌3000年 2019-04-22
  • 110| 962| 64| 652| 82| 958| 166| 597| 114| 962|