SQL SERVER BCP,SQL SERVER BULK INSERT

转自：https://www.cnblogs.com/fishparadise/p/4809014.html

前言

SQL SERVER提供多种不同的数据导出导入的工具，也可以编写SQL脚本，使用存储过程，生成所需的数据文件，甚至可以生成包含SQL语句和数据的脚本文件。各有优缺点，以适用不同的需求。下面介绍大容量数据导出导入的利器——BCP实用工具。同时在后面也介绍BULK INSERT导入大容量数据，以及BCP结合BULK INSERT做数据接口的实践（在SQL2008R2上实践）。

总结

--打开高级选项
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE;

--指定导入目的和导入源
EXEC master..xp_cmdshell 'BCP IMP_DATA.dbo.t_goods in E:\report.txt -c -T'

--导出基于查询
EXEC master..xp_cmdshell 'EXEC master..xp_cmdshell  'bcp "select * from db_tank..sys_users_goods where userid = 25324" queryout d:\goods.txt -c -Uroadadmin -Ppo@#r53e%$k8(+-3 -S127.0.0.1,2433 '
--导出基于结果
EXEC master..xp_cmdshell 'EXEC master..xp_cmdshell  'bcp db_tank.dbo.msg out d:\goods.txt -c -Uroadadmin -Ppo@#r53e%$k8(+-3 -S127.0.0.1,2433 '



EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE;
EXEC SP_CONFIGURE 'show advanced options', 0;
RECONFIGURE;

 

1. BCP的用法

BCP 实用工具可以在 Microsoft SQL Server 实例和用户指定格式的数据文件间大容量复制数据。使用 BCP实用工具可以将大量新行导入 SQL Server 表，或将表数据导入数据文件。除非与 queryout 选项一起使用，否则使用该实用工具不需要了解 Transact-SQL 知识。BCP既可以在CMD提示符下运行，也可以在SSMS下执行。

　　015

figure-1

语法：

bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

 BCP导出数据

简单的导出例子1：

bcp db.dbo.table out file_URL

　　018

figure-2

简单的导出例子2：

bcp "select ....." queryout file_URL

　　019

figure-3

在SSMS上同时也可以执行

--打开高级选项
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE with override;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE with override;;


--在SSMS里导出数据
EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:\T1_02.txt -c -T'
GO


--关闭执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE with override;;
--关闭高级选项
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE with override;

 

code-1　　

　　017

figure-4

 同理 queryout 也可以

--打开高级选项
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE with override;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE with override;;


--在SSMS里导出数据
EXEC [master]..xp_cmdshell
'BCP "SELECT * FROM TestDB_2005.dbo.T1" queryout E:\T1_03.txt -c -T'
GO
--关闭执行CMD命令 EXEC SP_CONFIGURE 'xp_cmdshell', 0; 
RECONFIGURE with override;; 

--关闭高级选项 
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE with override;

code-2

　　020

figure-5

从个人来讲，我更喜欢使用第二种跟queryout选项一起使用的写法，因为这样可以更加灵活控制要导出的数据。如果执行BCP命令遇到这样的错误提示：

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


基于安全的考虑，系统默认没有开启xp_cmdshell选项。使用下面语句开启此选项。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;
GO

 

code-3

 

使用完之后，可以把sp_cmdshell关闭。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE;
GO

 

code-4

BCP导入数据

　　bcp db.dbo.tab in file_URL

修改figure-2中的out为in即可，把数据导入。

　　022

figure-6

 

　　023

figure-7

 

使用BULK INSERT导入数据

BULK INSERT dbo.T1 FROM 'E:\T1.txt'
WITH (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'    
)

 

code-5

 　　024

figure-8

 

关于BULK INSERT更详细的说明，参考：https://msdn.microsoft.com/zh-cn/library/ms188365%28v=sql.105%29.aspx

相比BCP的导入，BULK INSERT提供更灵活的选择。

 

BCP几个常用的参数说名

database_name 指定的表或视图所在数据库的名称。如果未指定，则使用用户的默认数据库。
in | out| queryout | format

  • in 从文件复制到数据库表或视图。

  • out 从数据库表或视图复制到文件。如果指定了现有文件，则该文件将被覆盖。提取数据时，请注意 bcp 实用工具将空字符串表示为 null，而将 null 字符串表示为空字符串。

  • queryout 从查询中复制，仅当从查询大容量复制数据时才必须指定此选项。

  • format 根据指定的选项（-n、-c、-w 或 -N）以及表或视图的分隔符创建格式化文件。大容量复制数据时，bcp 命令可以引用一个格式化文件，从而避免以交互方式重复输入格式信息。format 选项要求指定 -f 选项；创建 XML 格式化文件时还需要指定 -x 选项。

    in 从文件复制到数据库表或视图。
    out 从数据库表或视图复制到文件。如果指定了现有文件，则该文件将被覆盖。提取数据时，请注意 bcp 实用工具将空字符串表示为 null，而将 null 字符串表示为空字符串。
    queryout 从查询中复制，仅当从查询大容量复制数据时才必须指定此选项。
-c 使用字符数据类型执行该操作。此选项不提示输入每个字段；它使用 char 作为存储类型，不带前缀；使用 \t（制表符）作为字段分隔符，使用 \r\n（换行符）作为行终止符。
-w 使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段；它使用 nchar 作为存储类型，不带前缀；使用 \t（制表符）作为字段分隔符，使用 \n（换行符）作为行终止符。
-tfield_term 指定字段终止符。默认值为 \t（制表符）。使用此参数可以替代默认字段终止符。
-rrow_term 指定行终止符。默认值为 \n（换行符）。使用此参数可替代默认行终止符。
-Sserver_name[ \instance_name] 指定要连接的 SQL Server 实例。如果未指定服务器，则 bcp 实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机中运行 bcp 命令，则必须使用此选项。若要连接到服务器上的 SQL Server 默认实例，请仅指定 server_name。若要连接到 SQL Server 的命名实例，请指定 server_name\instance_name。
-Ulogin_id 指定用于连接到 SQL Server 的登录 ID。
-Ppassword 指定登录 ID 的密码。如果未使用此选项，bcp 命令将提示输入密码。如果在命令提示符的末尾使用此选项，但不提供密码，则 bcp 将使用默认密码 (NULL)。
-T 指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。不需要网络用户的安全凭据、login_id 和 password。如果未指定 –T，则需要指定 –U 和 –P 才能成功登录。

更详细的参数，请参考：https://msdn.microsoft.com/zh-cn/library/ms162802%28v=sql.105%29.aspx

 

 

 

2. 实践

2.1 导出数据

介绍完BCP的导出导入，以及BULK INSERT的导入，下面进行一些实际的操作。为了接近实际环境，创建一张10个字段的表，包含有几种常用的数据类型，构造2000万的数据，包含中文和英文。为了更快插入测试数据，先不创建索引。在执行下面代码之前，请留意下数据库的日志恢复模式是否设置为大容量模式或简单模式，以及磁盘空间是否足够（我的实践中，数据生成后数据文件和日志文件大概需要40G的空间）。

USE AdventureWorks2008R2
GO

IF OBJECT_ID(N'T1') IS NOT NULL
BEGIN
    DROP TABLE T1
END
GO

CREATE TABLE T1 (
    id_ INT,
    col_1 NVARCHAR(50),
    col_2 NVARCHAR(40),
    col_3 NVARCHAR(40),
    col_4 NVARCHAR(40),
    col_5 INT,
    col_6 FLOAT,
    col_7 DECIMAL(18,8),
    col_8 BIT,
    input_date DATETIME DEFAULT(GETDATE())
)
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a,master.sys.all_objects AS b,sys.databases AS c
WHERE c.database_id <= 5
)

,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T1 (id_,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8)
SELECT row_no,REPLICATE(N'博客园 ',10),NEWID(),NEWID(),NEWID(),CAST(row_no * RAND() * 10 AS INT),row_no * RAND(),row_no * RAND(),CAST(row_no * RAND() AS INT) % 2
FROM CTE2 WHERE row_no <= 20000000
GO

 

code-6

 

过程要花上几分钟的时间才能完成，请耐心等待一下。关于数据的构造，可以参考我的另一篇博文：http://www.cnblogs.com/fishparadise/p/4781035.html

使用上面介绍的用法导出数据：

EXEC [master]..xp_cmdshell
'BCP AdventureWorks2008R2.dbo.T1 out E:\T1_04.txt -w -T -S KEN\SQLSERVER08R2'
GO

 

code-7

这里使用-w参数。BCP可以在CMD下导出数据，测试导出2000万条记录，我的笔记本使用了近8分钟左右的时间。BCP同时也可以在SSMS中执行，使用了6分多钟时间，比CMD下速度要快些，生成的文件大小一致，每个文件近5GB　　　　　　　　

　　014

figure-9

　　 021

figure-10

 

而对于复杂的大容量导入情况，通常都会需要格式化文件。在以下情况下，必须使用格式化文件：

  • 具有不同架构的多个表使用同一数据文件作为数据源。

  • 数据文件中的字段数不同于目标表中的列数；例如：

    • 目标表中至少包含一个定义了默认值或允许为 NULL 的列。

    • 用户不具有对目标表的一个或多个列的 SELECT/INSERT 权限。

    • 具有不同架构的两个或多个表使用同一个数据文件。

     

  • 数据文件和表的列顺序不同。

  • 数据文件列的终止字符或前缀长度不同。

 

这里不使用格式化文件进行导出导入的演示了。详细介绍与使用，请参考联机丛书。

 

2.2 导入数据

使用BULK INSERT把数据导入到目标表数据。为提高性能，可临时删除索引，导完之后再重建索引等。请注意要预留足够的磁盘空间。这里大概花了15分钟导完。

　　025

figure-11

2.3 bcp导入本地文本到远程机器

BCP test1.dbo.customer in c:\tmp\customer1.txt -c -E  -S 192.168.191.80 -U dba -P "a123456!"

注意 -E 是忽略标识符（主键），如果冲突则忽略该行

2.4 bcp 结合bat批量导入多个文件

@echo off
setlocal enabledelayedexpansion        rem 设定变量延迟的效果，为了下面的计数 !sum!
set DestPath=D:\tools\Bcp\csv
set DestExt=*.csv             rem 模糊检索.csv文件
set /a sum=0 Rem  /a 参数是等号右边数字表达式
for /f "delims=" %%i  in ('dir /b/a-d/s %DestPath%\%DestExt%')      rem 循环指定文件夹下的文件，注意dir 后面的参数
do (
echo,                    rem 输出空行(效率高)
set /a sum+=1
echo ------------------------------------!sum!------------------------------------
echo %%i
bcp Mydb.dbo.TB_Test in %%i -t "\," -c -S 172.168.1.2 -U sa -P 123456    rem BCP导入数据文件到数据库，一定要主要格式-t -c，符合你的文本格式，否则导入报错
)

pause

 把执行的log记录下来，可以另外新建个bat文件，调用上面创建的bat，如下实现：

call input.bat => aa.log
echo "Complete!!!"
pause

 

2.5 bulk insert 从URL 路径导入到本地

-- 从URL远程机器上导入

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW=2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

 

3. 扩展

3.1 数据导出导入自动化与数据接口

由于工作关系，有时要开发一些客户的数据接口，每天自动导入比较大量的数据。限制于应用程序等因素影响，所以考虑直接使用SQL SERVER的BULK INSERT每天自动去读取相关目录的中间文件。尽管目录是动态的，但由于中间文件是固定格式的，通过编写动态SQL，最后封装成存储过程，放到JOB中，配置运行的计划，即可完成自动化的工作。下面简单演示下过程：

 

3.1.1 编写导入脚本

CREATE PROCEDURE sp_import_data
AS
BEGIN 
DECLARE @path NVARCHAR(500)
DECLARE @sql NVARCHAR(MAX)
/*S_PARAMETERS表是可以在应用程序上配置路径的*/
SELECT  @path = value_ + CONVERT(NVARCHAR, getdate(), 23) + '.txt' FROM S_PARAMETERS WHERE [type] = 'Import'
/*T4是一张临时的中间表。先把数据从文件中读入到中间表，最后通过脚本把T4中间表的数据插入到实际的业务表中*/
SET @sql=N'BULK INSERT T4 FROM '''+ @path + '''
WITH (
    FIELDTERMINATOR = ''*'',
    ROWTERMINATOR = ''\n''
    
)'
EXEC (@sql)
END
GO

 

code-8

 

3.1.2 配置JOB

首先要配置好的是SQL SERVER有权限读取相关目录和文件的权限。在Sql Server Configuration Manager --> SQL Server Services 选择相应的实例，右键选择属性，在Log On页签，使用有足够权限启动SQL SERVER和有权限读取相关目录的用户，比如读取网络盘。

　　

figure-12

 

在SQL Server Agent新建一个作业

　　026

figure-13

 

在General页，选择Owner，这里选择sa。

　　027

figure-14

 

在Steps页，在Command里执行写好的存储过程。

　　028

figure-15

 

在Schedules页，配置执行的时间和频率等。完成。

　　029

figure-16

 

 

3.2 高版本数据库降级到低版本

一般来说，从低版本备份的数据库可以直接在高版本的数据库中恢复的，比如SQL2000的备份可以在SQL2005或SQL2008中恢复，除非是跨度太大的之外。比如SQL2000的备份就不能直接在SQL2012中恢复，只能恢复到SQL2008，再从SQL2008备份出来，最后到SQL2012上恢复。

而高版本的备份一般不能在低版本中恢复，如SQL2008的备份不能在SQL2005或SQL2000中恢复。而实际中，却又会遇到这种需求。最好是通过高版本SSMS直接连接两个不同版本的数据库，通过数据库间的数据导出导入或写脚本，把高版本的数据导到低版本的数据库中。这是比较快速安全的方法。但是如果两个版本的数据库不能相连，只能是把数据导出来，再导入。对于数据量不大来说，使用SSMS的导出导入功能，或是生成包含数据的脚本即可（下图）。对于大数据来说，却是一个灾难，如前面有2000万数据的大表，生成数据的脚本也有几个G大，直接使用SSMS执行是不可能的了。只能是使用SQLCMD实用工具，在后台执行SQL脚本，或者借助BCP、BULK INSERT等这种大容量数据导出导入的工具。

　　005

figure-17

 

4.深入bulk insert

官网：https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver15

（4.1）基本语法

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] DATA_SOURCE = 'data_source_name' ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]

   -- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]

（4.2）基本实践

更详细参考：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

Sql server 的bulk insert语句可以高效的导入大数据量的平面文件（txt，csv文件）到数据库的一张表中，其用法如下：

bulk insert test
from 'f:\test.txt'
with
(fieldterminator=',',
rowterminator='\n')

其中

　　"test"是数据库表的名字，

　　"f:\test.txt"是导入平面文件的地址，

　　fieldterminator指定平面文件中列的分隔符是什么，

　　rowterminator指定平面文件中行的结束符是什么。

从URL远程机器上导入

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW=2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

核心字段功能：

　　FIRSTROW = first_row 指定要加载的第一行的行号。 默认值是指定数据文件中的第一行。 FIRSTROW 从 1 开始。

　　FIELDQUOTE = 'field_quote' 适用范围 ：SQL Server 2017 (14.x)CTP 1.1。 指定将用作 CSV 文件引号字符的字符。 如果未指定，根据 RFC 4180 标准中的定义，引号字符 (") 将用作引号字符。　

　　FIELDTERMINATOR ='field_terminator' 指定要用于 char 和 widechar 数据文件的字段终止符 。 默认字段终止符为 \t（制表符）

　　ROWTERMINATOR ='row_terminator' 指定要用于 char 和 widechar 数据文件的行终止符 。 默认行终止符为 \r\n（换行符）

 

还可以使用FIRSTROW和LASTROW限制行数。如下COPY前三行：

bulk insert test
from 'f:\test.txt'
with
(fieldterminator=',',
rowterminator='\n',
FIRSTROW =1,
LASTROW=3)

（4.3）把文件中的3个字段导入到有7个字段的表中去

要把平面文件数据导入到数据库表中，平面文件只有3个字段，数据库表有7个字段，
怎么把平面文件字段的对应到表的字段，如何用bulk insert来实现?

数据库表 userinfo

CREATE TABLE userinfo
(
id INT identity,
userName varchar(20),
pass varchar(20),
address varchar(100),
phone varchar(20),
email varchar(128),
registerTime datetime
)

平面文件数据是F:\test.txt

userName，address，phone 
hua，湖南，5971898
jan，重庆，2334512
wang, 北京, 100201

先在G盘存放一个格式化xml文件
G:\format.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="userName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="address" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="phone" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>


然后使用bulk insert语句时，采用FORMATFILE参数指定该format.xml文件

BULK INSERT userinfo
FROM 'F:\test.txt'
WITH
(
FORMATFILE = 'G:\format.xml',
FIELDTERMINATOR=',',
ROWTERMINATOR='\n',
FIRSTROW = 2
)

FORMATFILE = 'format_file_path' 指定格式化文件的完整路径。 描述数据文件的格式化文件，数据文件中包含存储的响应，而存储的响应则是使用 bcp 实用工具在同一表或视图中创建的。 在下列情况下应使用格式化文件：

这样bulk insert语句就会按照format.xml文件中的列映射，将平面文件F:\test.txt的三列数据，依次插入到表userinfo的userName、address、phone这三个字段上了。

疑难解答

1.最好使用十六进制ASCII码来声明ROWTERMINATOR

有时候当我们的txt或csv文件的文件头没有声明文件的编码方式（比如txt或csv文件是936-GBK或者UTF-8等编码方式，但是在其文件头没有声明）时，会导致bulk insert语句的ROWTERMINATOR参数失效，比如当我在导入一个test.txt文件是936-GBK编码方式的时候，我发现明明文件中的换行符是'\n'，但是当我声明ROWTERMINATOR='\n'的时候根本不起作用，bulk insert老是找不到文件中的换行符进而报错。

 

然后我通过网上查资料发现，原来bulk insert的ROWTERMINATOR参数，可以使用十六进制ASCII码来表示换行符是什么。比如'\n'的ASCII码是十进制12，十六进制0A，那么我们可以在bulk insert语句中声明0x0a来作为ROWTERMINATOR表示换行符'\n'，如下所示：

BULK INSERT userinfo
FROM 'F:\test.txt'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR='0x0a',
FIRSTROW =1,
LASTROW=1000,
CODEPAGE='936'
)BULK INSERT userinfo
FROM 'F:\test.txt'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR='0x0a',
FIRSTROW =1,
LASTROW=1000,
CODEPAGE='936'
)

实验发现当我用十六进制ASCII码声明bulk insert的ROWTERMINATOR参数后，sql server成功识别出了文件中每一行结束的位置。同样如果换行符是'\r'，那么我们可以声明ROWTERMINATOR为十六进制ASCII码0x0d。所以当你使用常规字符（例如'\n'、'\r'等）来给bulk insert的ROWTERMINATOR参数赋值不起作用的时候，你可以尝试使用字符的十六进制ASCII码来给ROWTERMINATOR参数赋值，bulk insert的ROWTERMINATOR参数可以根据十六进制ASCII码准确识别出数据文件中的换行符。

2.使用CODEPAGE参数声明文件的编码方式

CODEPAGE参数可以声明txt或csv文件的编码方式是什么，有时候bulk insert无法识别出导入文件的编码方式，会导致从文件导入的数据是乱码，这时候如果我们知道文件的编码方式，就可以使用CODEPAGE参数告诉bulk insert文件使用的是什么编码，从而避免数据导入到数据库后变为乱码。比如上面的sql中我们就用CODEPAGE参数声明了导入文件的编码是936（GBK）

5、有标识列 identity 的情况下

（5.1）bcp

如下图，我们运行2次

　　

 

使用了自增id

 　　

 

bcp 使用 -E ，可以直接插入标识列（就可以把导入文件中的 自增列对应的列值，插入到表中去），否则会使用自增Id；

而且使用了 -E 之后，如果有主键冲突，会忽略跳过对应行内容，不会报错影响其他不冲突的行；

　　

 

 　　 

（5.2）bulk insert

参考官网：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

bulk insert的话，加上 KEEPIDENTITY 参数，就可以把导入文件中的 自增列（标识列）对应的列值，插入到表中去

而且使用了 KEEPIDENTITY  之后，如果有主键冲突，会忽略跳过对应行内容，不会报错影响其他不冲突的行；

BULK INSERT dbo.customer FROM 'd:\customer.txt'
WITH (
　　FIELDTERMINATOR = '\t',
　　ROWTERMINATOR = '\n' ,
　　KEEPIDENTITY
)

这里结果与上面bcp 结果一样，就不演示图了；

【总结】

使用BCP并结合BULK INSERT可实现大容量数据的快速导出导入，并可以实现其自动化工作。对于少量数据来说，操作也不算很复杂。这是除了SSMS上的图形化工具之外，又一个非常实用的工具。

 

参考文档

https://blog.csdn.net/liguiping2000/article/details/7417806

posted @ 2019-04-01 16:29  郭大侠1  阅读(6997)  评论(0)  编辑  收藏  举报