BCP導出導入大容量資料實踐

前言

SQL SERVER提供多種不同的資料導出導入的工具，也可以編寫SQL腳本，使用存儲過程，生成所需的資料檔案，甚至可以生成包含SQL語句和資料的腳本檔案。各有優缺點，以适用不同的需求。下面介紹大容量資料導出導入的利器——BCP實用工具。同時在後面也介紹BULK INSERT導入大容量資料，以及BCP結合BULK INSERT做資料接口的實踐（在SQL2008R2上實踐）。

1. BCP的用法

BCP 實用工具可以在 Microsoft SQL Server 執行個體和使用者指定格式的資料檔案間大容量複制資料。使用 BCP實用工具可以将大量新行導入 SQL Server 表，或将表資料導入資料檔案。除非與 queryout 選項一起使用，否則使用該實用工具不需要了解 Transact-SQL 知識。BCP既可以在CMD提示符下運作，也可以在SSMS下執行。

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]"]

簡單的導出例子1：

figure-2

簡單的導出例子2：

figure-3

在SSMS上同時也可以執行：

EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:\T1_02.txt -c -T'
GO

code-1

figure-4

EXEC [master]..xp_cmdshell
'BCP "SELECT * FROM TestDB_2005.dbo.T1" queryout E:\T1_03.txt -c -T'
GO

code-2

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導入資料

修改figure-2中的out為in即可，把資料導入。

figure-6

figure-7

使用BULK INSERT導入資料

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

code-5

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 選項。
-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。

figure-9

figure-10

而對于複雜的大容量導入情況，通常都會需要格式化檔案。在以下情況下，必須使用格式化檔案：

具有不同架構的多個表使用同一資料檔案作為資料源。
資料檔案中的字段數不同于目标表中的列數；例如：
- 目标表中至少包含一個定義了預設值或允許為 NULL 的列。
- 使用者不具有對目标表的一個或多個列的 SELECT/INSERT 權限。
- 具有不同架構的兩個或多個表使用同一個資料檔案。
資料檔案和表的列順序不同。
資料檔案列的終止字元或字首長度不同。

這裡不使用格式化檔案進行導出導入的示範了。詳細介紹與使用，請參考聯機叢書。

2.2 導入資料

使用BULK INSERT把資料導入到目标表資料。為提高性能，可臨時删除索引，導完之後再重建索引等。請注意要預留足夠的磁盤空間。這裡大概花了15分鐘導完。

figure-11

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建立一個作業

figure-13

在General頁，選擇Owner，這裡選擇sa。

figure-14

在Steps頁，在Command裡執行寫好的存儲過程。

figure-15

在Schedules頁，配置執行的時間和頻率等。完成。

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等這種大容量資料導出導入的工具。

figure-17

4. 總結

使用BCP并結合BULK INSERT可實作大容量資料的快速導出導入，并可以實作其自動化工作。對于少量資料來說，操作也不算很複雜。這是除了SSMS上的圖形化工具之外，又一個非常實用的工具。

BCP導出導入大容量資料實踐

繼續閱讀

SqlServer同步資料庫-分發-訂閱

幾種SqlServer資料庫分頁方式

SQLServer資料庫比對工具用什麼好。

C#連接配接sqlserver資料庫

我的職業生涯（四）

.NET開發人員的瓶頸和職業發展

資料庫拼接列字段拼接列字段

windows8.1上安裝sqlserver2005後找不到啟動程式

hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置

asp連接配接資料庫的代碼方法

MS SQL Server 資料庫（基礎篇）

SQL Server不存在或通路被拒絕"問題的解決"SQL Server不存在或通路被拒絕"問題的解決

asp 連接配接資料庫的方法

ms sqlserver常用sql語句

金蝶雲星空即時庫存查詢SQL語句SQLServer

解決"T-SQL無法附加到程序"錯誤的補充,T-SQLcan not attach to process