SQL Server Bulk Insert如何批量数据导入

109次阅读

共计 2778 个字符，预计需要花费 7 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 SQL Server Bulk Insert 如何批量数据导入，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

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 表可以不存在