SQL Server的Bulk Insert语句可以将本地或远程的数据文件批量导入到数据库中,速度非常的快。远程文件必须共享才行,文件路径须使用通用约定(UNC)名称,即"\\服务器名或IP\共享名\路径\文件名"的形式。
* 1. 由于Bulk Insert通常配合格式化文件批量导入数据更方便,所以这里先介绍bcp工具导出格式化文件的方法。
bcp是SQL Server提供的命令行实用工具提供了数据的导出、导入、格式文件导出等功能,导出格式化文件的语法如下:
- bcp 数据库名.用户名.表名 format nul -- 这里的nul必须存在,用于不是导出和导入数据的情况下
- -f 输出的格式化文件名 [-x] -c -- -x参数指定输出的格式文件为xml格式(默认非xml格式); -c参数指定数据存储方式为字符,并默认指定'\t'作为字段间隔符;'\n'作为行间隔符
- [-t 字段间隔符] [-r 行间隔符号] -- -t与-r参数可选,用于覆盖-c指定的默认间隔符
- -T -- 指定数据库连接可信,即使用Windows身份登录
* 2. Bulk Insert
根据格式文件导入数据文件,语法格式如下:
- Bulk insert 数据库名.用户名.表名
- from '数据文件路径'
- with
- (
- formatfile = '格式文件路径',
- FirstRow = 2 --指定数据文件中开始的行数,默认是1
- )
* 3. OPENRORWSET(BULK)函数
有时,使用OPENROWSET(BULK)函数可以更灵活地选取想要的字段插入到原表或者其他表中,其语法格式为:
- 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
当然,该函数也可以这么使用:
- 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)创建数据库、表并填充测试数据,脚本如下:
- -- 创建数据库
- 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 出于安全目的默认将该存储过程禁用了,开启方法如下:
- --开启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导出格式文件:
- EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'
4)使用bcp导出数据文件:
- 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语句批量导入数据文件:
- BULK INSERT db_mgr.dbo.T_Student
- FROM 'C:/student.data'
- WITH
- (
- FORMATFILE = 'C:/student_fmt.xml'
- )
6)使用OPENROWSET(BULK)的例子:
- 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 实用工具:
相关推荐
SQL SERVER BULK INSERT用法
主要介绍了sql server Bulk Insert命令详细 ,需要的朋友可以参考下
上午在找Bulk Insert的资料看,还转了一篇。不巧今天下午就用上了,我遇到的需求是导出表A中的N个字段,然后导入到表B的N个字段当中。
使用BULK INSERT大批量导入数据 SQLSERVER,需要的朋友可以参考下。
Z.SqlBulkCopy 支持bulkupdate、bulkinsert、bulkcopy、bulkDelete等扩展方法, 可以方便的将外部数据批量导入、批量合并导入、批量更新导入、批量删除到Sqlserver数据库,基本支持Sqlserver最新的数据库,我用的是...
有时候我们可能会把CSV中的数据导入...我们使用的是SQL Server的BULK INSERT命令,关于该命令的详细解释,请点击此处; 我们先在SQL Server中建立用于保存该信息的一张数据表, CREATE TABLE CSVTable( Name NVARCHA
11.5 使用BULK INSERT 命令 第12章 链接服务器与分布式事务 12.1 使用链接服务器和分布式数据 12.1.1 使用分布式查询 12.1.2 使用分布式事务 12.1.3 运行分布式事务处理协调器服务 12.2 管理链接服务器 12.2.1 添加...
11.5 使用BULK INSERT 命令 第12章 链接服务器与分布式事务 12.1 使用链接服务器和分布式数据 12.1.1 使用分布式查询 12.1.2 使用分布式事务 12.1.3 运行分布式事务处理协调器服务 12.2 管理链接服务器 12.2.1 添加...
15.3 BULK INSERT 15.4 OPENROWSET(BULK) 15.5 小结 第16章 开始集成 16.1 理解问题 16.2 包的综述 16.3 创建简单的包 16.4 执行包 16.5 小结 第17章 复制 17.1 复制的基础知识 17.2...
11.5 使用BULK INSERT 命令 第12章 链接服务器与分布式事务 12.1 使用链接服务器和分布式数据 12.1.1 使用分布式查询 12.1.2 使用分布式事务 12.1.3 运行分布式事务处理协调器服务 12.2 管理链接服务器 12.2.1 添加...
15.3 BULK INSERT 15.4 OPENROWSET(BULK) 15.5 小结 第16章 开始集成 16.1 理解问题 16.2 包的综述 16.3 创建简单的包 16.4 执行包 16.5 小结 第17章 复制 17.1 复制的基础知识 17.2...
本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中;第二种方法使用的是SqlBulkCopy,使... 此例子为控制台输出程序,有两个类,一个为BulkData类,主要实现了表值参数和sqlbulk
Nuget包: SqlServer.Util.Library BulkInsert使在连接内或连接间复制许多行变得容易。 | SqlMigrator提供克隆或“深层复制”功能。 | | ViewMaterializer提供了一种利用SQL Server更改跟踪来优化慢速视图的方法...
Z.SqlBulkCopy 支持bulkupdate、bulkinsert、bulkcopy、bulkDelete等扩展方法, 可以方便的将外部数据批量导入、批量合并导入、批量更新导入、批量删除到Sqlserver数据库,基本支持Sqlserver最新的数据库,我用的是...
Coverage is also given to the broader range of tools such as OPENDATASOURCE, linked servers, OPENROWSET, Migration Assistant for Access, BCP Import, and BULK INSERT just to name a few. If you're ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
SQL Server专家的呕心力作,数据库管理员的实战宝典,全面、深入地剖析SQL Server2008新特性,结构独特,实例丰富,操作性强。 编辑本段 目录 第Ⅰ部分 SQL Server 2008管理基础 第1章 SQL Server 2008管理...