BCP匯出匯入 SQL SERVER 大容量資料實踐教程

本教程我們介紹大容量資料匯出匯入的利器——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', 1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1RECONFIGUREGO

code-3

 
使用完之後，可以把sp_cmdshell關閉。

EXEC sp_configure 'show advanced options', 1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 0RECONFIGUREGO

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，而將 null 字串表示為空白字串。 queryout 從查詢中複製，僅當從查詢大量複製資料時才必須指定此選項。 format 根據指定的選項（-n、-c、-w 或 -N）以及表或視圖的分隔字元建立格式檔案。大量複製資料時，bcp 命令可以引用一個格式檔案，從而避免以互動方式重複輸入格式資訊。format 選項要求指定 -f 選項；建立 XML 格式檔案時還需要指定 -x 選項。 in 從檔案複製到資料庫表或視圖。 out 從資料庫表或視圖複製到檔案。如果指定了現有檔案，則該檔案將被覆蓋。提取資料時，請注意 bcp 工具 + 生產力將Null 字元串表示為 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 AdventureWorks2008R2GOIF OBJECT_ID(N'T1') IS NOT NULLBEGIN    DROP TABLE T1ENDGOCREATE 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()))GOWITH CTE1 AS ( SELECT a.[object_id] FROM master.sys.all_objects AS a,master.sys.all_objects AS b,sys.databases AS cWHERE 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) % 2FROM CTE2 WHERE row_no <= 20000000GO

code-6
 
過程要花上幾分鐘的時間才能完成，請耐心等待一下。

使用上面介紹的用法匯出資料：

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_dataASBEGIN 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)ENDGO

code-8

 
3.1.2 配置JOB

首先要配置好的是SQL SERVER有許可權讀取相關目錄和檔案的許可權。在Windows服務裡，開啟SQL SERVER的屬性，在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的備份不能在SQL2008或SQL2000中恢複。而實際中，卻又會遇到這種需求。最好是通過高版本SSMS直接連接兩個不同版本的資料庫，通過資料庫間的資料匯出匯入或寫指令碼，把高版本的資料導到低版本的資料庫中。這是比較快速安全的方法。但是如果兩個版本的資料庫不能相連，只能是把資料匯出來，再匯入。對於資料量不大來說，使用SSMS的匯出匯入功能，或是產生包含資料的指令碼即可（下圖）。對於大資料來說，卻是一個災難，如前面有2000萬資料的大表，產生資料的指令碼也有幾個G大，直接使用SSMS執行是不可能的了。只能是使用BCP、BULK INSERT這種大容量資料匯出匯入的工具。

figure-17

 

4. 總結

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

使用 bcp 工具 + 生產力匯入和匯出大容量資料

本主題概述了使用 bcp 工具 + 生產力從 SQL Server 資料庫中可使用 SELECT 語句的任意位置（包括分區視圖）匯出資料的過程。

bcp 工具 + 生產力 (Bcp.exe) 是一個使用大量複製程式 (BCP) API 的命令列工具。bcp 工具 + 生產力可執行以下任務：

    將 SQL Server 表中的資料大容量匯出到資料檔案中。
    從查詢中大容量匯出資料。
    將資料檔案中的資料大容量匯入到 SQL Server 表中。
    產生格式檔案。

通過 bcp 命令訪問 bcp 工具 + 生產力。使用 bcp 命令大容量匯入資料時，除非使用已有的格式檔案，否則必須瞭解表的架構及其各列的資料類型。

bcp 工具 + 生產力可將 SQL Server 表中的資料匯出到資料檔案，以供其他程式使用。此工具 + 生產力還可將其他程式（通常為另一資料庫管理系統 (DBMS)）中的資料匯入 SQL Server 表。資料首先從來源程式匯出到資料檔案，然後再通過單獨的操作將資料檔案中的資料複製到 SQL Server 表中。

bcp 命令具有可指定資料檔案的資料類型和其他資訊的開關。如果未指定這些開關，則此命令會提示您指定格式資訊，例如資料檔案中資料欄位的類型。然後此命令會詢問您是否要建立包含互動式響應的格式檔案。如果希望在以後的大容量匯入或大容量匯出操作中具有靈活性，格式檔案通常會很有用。可以在稍後對同等資料檔案使用 bcp 命令時指定該格式檔案。有關詳細資料，請參閱使用 bcp 指定資料格式以獲得相容性。

注意 注意

從 MicrosoftSQL Server 7.0 版開始，使用 ODBC 大量複製 API 編寫 bcp 工具 + 生產力。早期版本的 bcp 是使用 DB-Library 大量複製 API 編寫的。