用代码从文件中导入数据到SQL Server
引言
导入数据到SQL Server 是常见的需求，特别是定期导入这种需求。
对于定期导入主要有以下几种方式可选择：
- Bulk Insert
- Bcp Utility
- OpenRowSet
- 写程序导入（使用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 中读取数据：
