前言
SQL SERVER提供多種不同的數據導出導入的工具，也可以編寫(xiě)SQL腳本，使用存儲過(guò)程，生成所需的數據文件，甚至可以生成包含SQL語(yǔ)句和數據的腳本文件。各有優(yōu)缺點(diǎn)，以適用不同的需求。下面介紹大容量數據導出導入的利器——BCP實(shí)用工具。同時(shí)在后面也介紹BULK INSERT導入大容量數據，以及BCP結合BULK INSERT做數據接口的實(shí)踐（在SQL2008R2上實(shí)踐）。
1. BCP的用法
BCP 實(shí)用工具可以在 Microsoft SQL Server 實(shí)例和用戶(hù)指定格式的數據文件間大容量復制數據。使用 BCP實(shí)用工具可以將大量新行導入 SQL Server 表，或將表數據導入數據文件。除非與 queryout 選項一起使用，否則使用該實(shí)用工具不需要了解 Transact-SQL 知識。BCP既可以在CMD提示符下運行，也可以在SSMS下執行。
figure-1
語(yǔ)法：
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]"]
簡(jiǎn)單的導出例子1：
figure-2
簡(jiǎn)單的導出例子2：
figure-3
在SSMS上同時(shí)也可以執行：
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
從個(gè)人來(lái)講，我更喜歡使用第二種跟queryout選項一起使用的寫(xiě)法，因為這樣可以更加靈活控制要導出的數據。如果執行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.
基于安全的考慮，系統默認沒(méi)有開(kāi)啟xp_cmdshell選項。使用下面語(yǔ)句開(kāi)啟此選項。
EXEC sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDE;GOEXEC sp_configure 'xp_cmdshell', 1RECONFIGURE WITH OVERRIDE;GO
code-3
使用完之后，可以把sp_cmdshell關(guān)閉。
EXEC sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDE;GOEXEC sp_configure 'xp_cmdshell', 0RECONFIGURE 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
關(guān)于BULK INSERT更詳細的說(shuō)明，參考：
https://msdn.microsoft.com/zh-cn/library/ms188365%28v=sql.105%29.aspx相比BCP的導入，BULK INSERT提供更靈活的選擇。
BCP幾個(gè)常用的參數說(shuō)明：
database_name指定的表或視圖所在數據庫的名稱(chēng)。如果未指定，則使用用戶(hù)的默認數據庫。
in | out| queryout | format
in 從文件復制到數據庫表或視圖。
out 從數據庫表或視圖復制到文件。如果指定了現有文件，則該文件將被覆蓋。提取數據時(shí)，請注意 bcp 實(shí)用工具將空字符串表示為 null，而將 null 字符串表示為空字符串。
queryout 從查詢(xún)中復制，僅當從查詢(xún)大容量復制數據時(shí)才必須指定此選項。
format 根據指定的選項（-n、-c、-w 或 -N）以及表或視圖的分隔符創(chuàng )建格式化文件。大容量復制數據時(shí)，bcp 命令可以引用一個(gè)格式化文件，從而避免以交互方式重復輸入格式信息。format 選項要求指定 -f 選項；創(chuàng )建 XML 格式化文件時(shí)還需要指定 -x 選項。
in 從文件復制到數據庫表或視圖。
out 從數據庫表或視圖復制到文件。如果指定了現有文件，則該文件將被覆蓋。提取數據時(shí)，請注意 bcp 實(shí)用工具將空字符串表示為 null，而將 null 字符串表示為空字符串。
queryout 從查詢(xún)中復制，僅當從查詢(xún)大容量復制數據時(shí)才必須指定此選項。
-c使用字符數據類(lèi)型執行該操作。此選項不提示輸入每個(gè)字段；它使用 char 作為存儲類(lèi)型，不帶前綴；使用 \t（制表符）作為字段分隔符，使用 \r\n（換行符）作為行終止符。
-w使用 Unicode 字符執行大容量復制操作。此選項不提示輸入每個(gè)字段；它使用 nchar 作為存儲類(lèi)型，不帶前綴；使用 \t（制表符）作為字段分隔符，使用 \n（換行符）作為行終止符。
-tfield_term指定字段終止符。默認值為 \t（制表符）。使用此參數可以替代默認字段終止符。
-rrow_term指定行終止符。默認值為 \n（換行符）。使用此參數可替代默認行終止符。
-Sserver_name[ \instance_name]指定要連接的 SQL Server 實(shí)例。如果未指定服務(wù)器，則 bcp 實(shí)用工具將連接到本地計算機上的默認 SQL Server 實(shí)例。如果從網(wǎng)絡(luò )或本地命名實(shí)例上的遠程計算機中運行 bcp 命令，則必須使用此選項。若要連接到服務(wù)器上的 SQL Server 默認實(shí)例，請僅指定 server_name。若要連接到 SQL Server 的命名實(shí)例，請指定 server_name\instance_name。
-Ulogin_id指定用于連接到 SQL Server 的登錄 ID。
-Ppassword指定登錄 ID 的密碼。如果未使用此選項，bcp 命令將提示輸入密碼。如果在命令提示符的末尾使用此選項，但不提供密碼，則 bcp 將使用默認密碼 (NULL)。
-T指定 bcp 實(shí)用工具通過(guò)使用集成安全性的可信連接連接到 SQL Server。不需要網(wǎng)絡(luò )用戶(hù)的安全憑據、login_id 和 password。如果未指定 –T，則需要指定 –U 和 –P 才能成功登錄。
更詳細的參數，請參考：
https://msdn.microsoft.com/zh-cn/library/ms162802%28v=sql.105%29.aspx2. 實(shí)踐
2.1 導出數據
介紹完BCP的導出導入，以及BULK INSERT的導入，下面進(jìn)行一些實(shí)際的操作。為了接近實(shí)際環(huán)境，創(chuàng )建一張10個(gè)字段的表，包含有幾種常用的數據類(lèi)型，構造2000萬(wàn)的數據，包含中文和英文。為了更快插入測試數據，先不創(chuàng )建索引。在執行下面代碼之前，請留意下數據庫的日志恢復模式是否設置為大容量模式或簡(jiǎn)單模式，以及磁盤(pán)空間是否足夠（我的實(shí)踐中，數據生成后數據文件和日志文件大概需要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
過(guò)程要花上幾分鐘的時(shí)間才能完成，請耐心等待一下。關(guān)于數據的構造，可以參考我的另一篇博文：
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萬(wàn)條記錄，我的筆記本使用了近8分鐘左右的時(shí)間。BCP同時(shí)也可以在SSMS中執行，使用了6分多鐘時(shí)間，比CMD下速度要快些，生成的文件大小一致，每個(gè)文件近5GB。
figure-9
figure-10
而對于復雜的大容量導入情況，通常都會(huì )需要格式化文件。在以下情況下，必須使用格式化文件：
具有不同架構的多個(gè)表使用同一數據文件作為數據源。
數據文件中的字段數不同于目標表中的列數；例如：
目標表中至少包含一個(gè)定義了默認值或允許為 NULL 的列。
用戶(hù)不具有對目標表的一個(gè)或多個(gè)列的 SELECT/INSERT 權限。
具有不同架構的兩個(gè)或多個(gè)表使用同一個(gè)數據文件。
數據文件和表的列順序不同。
數據文件列的終止字符或前綴長(cháng)度不同。
這里不使用格式化文件進(jìn)行導出導入的演示了。詳細介紹與使用，請參考聯(lián)機叢書(shū)。
2.2 導入數據
使用BULK INSERT把數據導入到目標表數據。為提高性能，可臨時(shí)刪除索引，導完之后再重建索引等。請注意要預留足夠的磁盤(pán)空間。這里大概花了15分鐘導完。
figure-11
3. 擴展
3.1 數據導出導入自動(dòng)化與數據接口
由于工作關(guān)系，有時(shí)要開(kāi)發(fā)一些客戶(hù)的數據接口，每天自動(dòng)導入比較大量的數據。限制于應用程序等因素影響，所以考慮直接使用SQL SERVER的BULK INSERT每天自動(dòng)去讀取相關(guān)目錄的中間文件。盡管目錄是動(dòng)態(tài)的，但由于中間文件是固定格式的，通過(guò)編寫(xiě)動(dòng)態(tài)SQL，最后封裝成存儲過(guò)程，放到JOB中，配置運行的計劃，即可完成自動(dòng)化的工作。下面簡(jiǎn)單演示下過(guò)程：
3.1.1 編寫(xiě)導入腳本
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是一張臨時(shí)的中間表。先把數據從文件中讀入到中間表，最后通過(guò)腳本把T4中間表的數據插入到實(shí)際的業(yè)務(wù)表中*/SET @sql=N'BULK INSERT T4 FROM '''+ @path + '''WITH ( FIELDTERMINATOR = ''*'', ROWTERMINATOR = ''\n'' )'EXEC (@sql)ENDGO
code-8
3.1.2 配置JOB
首先要配置好的是SQL SERVER有權限讀取相關(guān)目錄和文件的權限。在Sql Server Configuration Manager --> SQL Server Services 選擇相應的實(shí)例，右鍵選擇屬性，在Log On頁(yè)簽，使用有足夠權限啟動(dòng)SQL SERVER和有權限讀取相關(guān)目錄的用戶(hù)，比如讀取網(wǎng)絡(luò )盤(pán)。
figure-12
在SQL Server Agent新建一個(gè)作業(yè)
figure-13
在General頁(yè)，選擇Owner，這里選擇sa。
figure-14
在Steps頁(yè)，在Command里執行寫(xiě)好的存儲過(guò)程。
figure-15
在Schedules頁(yè)，配置執行的時(shí)間和頻率等。完成。
figure-16
3.2 高版本數據庫降級到低版本
一般來(lái)說(shuō)，從低版本備份的數據庫可以直接在高版本的數據庫中恢復的，比如SQL2000的備份可以在SQL2005或SQL2008中恢復，除非是跨度太大的之外。比如SQL2000的備份就不能直接在SQL2012中恢復，只能恢復到SQL2008，再從SQL2008備份出來(lái)，最后到SQL2012上恢復。
而高版本的備份一般不能在低版本中恢復，如SQL2008的備份不能在SQL2005或SQL2000中恢復。而實(shí)際中，卻又會(huì )遇到這種需求。最好是通過(guò)高版本SSMS直接連接兩個(gè)不同版本的數據庫，通過(guò)數據庫間的數據導出導入或寫(xiě)腳本，把高版本的數據導到低版本的數據庫中。這是比較快速安全的方法。但是如果兩個(gè)版本的數據庫不能相連，只能是把數據導出來(lái)，再導入。對于數據量不大來(lái)說(shuō)，使用SSMS的導出導入功能，或是生成包含數據的腳本即可（下圖）。對于大數據來(lái)說(shuō)，卻是一個(gè)災難，如前面有2000萬(wàn)數據的大表，生成數據的腳本也有幾個(gè)G大，直接使用SSMS執行是不可能的了。只能是使用SQLCMD實(shí)用工具，在后臺執行SQL腳本，或者借助BCP、BULK INSERT等這種大容量數據導出導入的工具。
figure-17
4. 總結
使用BCP并結合BULK INSERT可實(shí)現大容量數據的快速導出導入，并可以實(shí)現其自動(dòng)化工作。對于少量數據來(lái)說(shuō)，操作也不算很復雜。這是除了SSMS上的圖形化工具之外，又一個(gè)非常實(shí)用的工具。
