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

61次阅读
没有评论

共计 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 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计2778字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)