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

引言

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