用代码从文件中导入数据到SQL Server 2019-07-22

引言

导入数据到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.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 中读取数据:

 

Copyright © 2019 万家博登录官网 All Rights Reserved
李姐
地址:海南省海口市美兰区和平大道21号世博大厦2301房
全国统一热线:18836731042