SQL Server的Bulk Insert

neocai

SQL Server的Bulk Insert语句可以将本地或远程的数据文件批量导入到数据库中，速度非常的快。远程文件必须共享才行，文件路径须使用通用约定（UNC）名称，即"\\服务器名或IP\共享名\路径\文件名"的形式。
 
* 1. 由于Bulk Insert通常配合格式化文件批量导入数据更方便，所以这里先介绍bcp工具导出格式化文件的方法。
bcp是SQL Server提供的命令行实用工具提供了数据的导出、导入、格式文件导出等功能，导出格式化文件的语法如下：
Sql代码  

• bcp 数据库名.用户名.表名 format nul -- 这里的nul必须存在，用于不是导出和导入数据的情况下  
  
• -f 输出的格式化文件名 [-x] -c  -- -x参数指定输出的格式文件为xml格式（默认非xml格式）; -c参数指定数据存储方式为字符，并默认指定'\t'作为字段间隔符;'\n'作为行间隔符  
  
• [-t 字段间隔符] [-r 行间隔符号]  -- -t与-r参数可选，用于覆盖-c指定的默认间隔符  
  
• -T -- 指定数据库连接可信，即使用Windows身份登录  
  

 
* 2. Bulk Insert
 根据格式文件导入数据文件，语法格式如下：
Sql代码  

• Bulk insert 数据库名.用户名.表名  
  
• from '数据文件路径'  
  
• with  
  
• (  
  
• formatfile = '格式文件路径',  
  
• FirstRow = 2    --指定数据文件中开始的行数，默认是1  
  
• )  
  

 
* 3. OPENRORWSET(BULK)函数
有时，使用OPENROWSET(BULK)函数可以更灵活地选取想要的字段插入到原表或者其他表中，其语法格式为：
Sql代码  

• INSERT INTO to_table_name SELECT filed_name_list  
  
• FROM OPENROWSET(BULK N'path_to_data_file', FORMATFILE=N'path_to_format_file') AS new_table_name  
  

　　  
 当然，该函数也可以这么使用：
Sql代码  

• SELECT field_name_list INTO temp_table_name  
  
• FROM OPENROWSET(BULK N'path_to_data_file', FORMATFILE=N'path_to_format_file') AS new_table_name  
  

　　 
 
下面举一个完整的例子：
 
1）创建数据库、表并填充测试数据，脚本如下：
Sql代码  

• -- 创建数据库  
  
• CREATE DATABASE [db_mgr]  
  
• GO  
  
• --创建测试表  
  
• USE db_mgr  
  
• CREATE TABLE dbo.T_Student(  
  
•     F_ID [int] IDENTITY(1,1) NOT NULL,  
  
•     F_Code varchar(10) ,  
  
•     F_Name varchar(100) ,  
  
•     F_Memo nvarchar(500) ,  
  
•     F_Memo2 ntext ,  
  
•     PRIMARY KEY  (F_ID)  
  
• )   
  
• GO  
  
•   
  
• --填充测试数据  
  
• Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select  
  
• 'code001', 'name001', 'memo001', '备注001' union all select  
  
• 'code002', 'name002', 'memo002', '备注002' union all select  
  
• 'code003', 'name003', 'memo003', '备注003' union all select  
  
• 'code004', 'name004', 'memo004', '备注004' union all select  
  
• 'code005', 'name005', 'memo005', '备注005' union all select  
  
• 'code006', 'name006', 'memo006', '备注006'  
  

 2）我们可以使用SQL Server的master..xp_cmdshell存储过程将CMD的命令传给系统，这样就可以直接在SQL Server的查询处理器中直接输入bcp的命令，而不用切换到命令模式下执行。SQL Server 出于安全目的默认将该存储过程禁用了，开启方法如下：
Sql代码  

• --开启xp_cmdshell存储过程（开启后有安全隐患）  
  
• EXEC sp_configure 'show advanced options', 1;  
  
• RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;  
  
• EXEC sp_configure 'show advanced options', 0;  
  
• RECONFIGURE;  
  

 
3）使用bcp导出格式文件：
Sql代码  

• EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'  
  

 
4）使用bcp导出数据文件：
Sql代码  

• EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T'  
  
• truncate table db_mgr.dbo.T_Student -- 将表中数据清空  
  

  注意：在实际使用过程中，数据文件可以由程序生成，如日志记录等！
 
5）使用Bulk Insert语句批量导入数据文件：
Sql代码  

• BULK INSERT db_mgr.dbo.T_Student  
  
• FROM 'C:/student.data'  
  
• WITH  
  
• (  
  
•     FORMATFILE = 'C:/student_fmt.xml'  
  
• )  
  

 
6）使用OPENROWSET(BULK)的例子：
Sql代码  

• INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name  
  
• FROM OPENROWSET(BULK N'C:/student_c.data', FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name -- T_Student表必须已存在  
  
•   
  
• SELECT F_Code, F_Name INTO db_mgr.dbo.tt  
  
• FROM OPENROWSET(BULK N'C:/student_c.data', FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name -- tt表可以不存在  
  

 
 
参考：
使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据（尤其是关于安全的那部分，导入远程文件时应特别注意）：
http://msdn.microsoft.com/zh-cn/library/ms175915.aspx
创建格式化文件：
http://msdn.microsoft.com/zh-cn/library/ms191516.aspx
OPENROWSET (Transact-SQL)：
http://msdn.microsoft.com/zh-cn/library/ms190312.aspx
BULK INSERT (Transact-SQL)：
http://msdn.microsoft.com/zh-cn/library/ms188365.aspx
bcp 实用工具：
http://msdn.microsoft.com/zh-cn/library/ms162802.aspx