共计 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 表可以不存在
以上是“SQL Server Bulk Insert 如何批量数据导入”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!