您现在的位置是:网站首页> 内容页

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

  • 菲彩国际注册
  • 2019-01-07
  • 280人已阅读
简介引言导入数据到SQLServer是常见的需求,特别是定期导入这种需求。对于定期导入主要有以下几种方式可选择:BulkInsertBcpUtilityOpenRowSet写程序导

引言

导入数据到SQL Server 是常见的需求,特别是定期导入这种需求。

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

    Bulk InsertBcp UtilityOpenRowSet写程序导入(使用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或主机IPShareNamePathFileNameDataFileType:数据编码{ "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 导入数据到tableout 导出table 数据到文件queryout 导出查询结果到文件format nul 导出格式文件

使用举例:

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

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

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

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

-S:server name-T:SQL Server 信任模式,若不指定,则需用-U、-P指定数据库用户名,密码-t :字段分隔符,默认是tab字符,-t表示用逗号分隔-c:表数据是字符型,以tab字符为字段分隔符,以为行分隔符

bcp instance.dbo.table format nul -S servername -f FormatFile.fmt -T -t -cbcp instance.dbo.table out c:dataFile.csv -S servername -T -t -cbcp 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.0Data Source=databaseNameUser ID=MyUserIDPassword=MyPassword"ODBC:MSDASQL"Provider=MSDASQLDSN=dsnNameUID=MyUserIDPWD=MyPassword"Oracel:MSDAORA"Provider=MSDAORAData Source=serverNameUser ID=MyUserID Password=MyPassword"

更多请见MSDN参考

使用举例:

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

 

文章评论

Top