當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

BCP导出导入大容量数据实践

發布時間：2025/3/20 编程问答 53 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 GO EXEC?sp_configure?'xp_cmdshell',?1 RECONFIGURE GO

code-3

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

EXEC?sp_configure?'show?advanced?options',?1 RECONFIGURE GO EXEC?sp_configure?'xp_cmdshell',?0 RECONFIGURE 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 選項。 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 BEGINDROP?TABLE?T1 END GOCREATE?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 ) GOWITH?CTE1?AS?(? SELECT? ROW_NUMBER()?OVER?(ORDER?BY?a.[object_id]?ASC)?as?row_no, a.[object_id]? FROM?master.sys.all_objects?AS?aCROSS?JOIN?master.sys.all_objects?AS?bCROSS?JOIN?sys.databases?AS?c WHERE?c.database_id?<=?5 )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?CTE1? WHERE?row_no?<=?20GO

code-6

過程要花上幾分鐘的時間才能完成，請耐心等待一下。關于數據的構造，可以參考我的另一篇博文：http://fishparadise.blog.51cto.com/11284420/1750798

使用上面介紹的用法導出數據：

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上的圖形化工具之外，又一個非常實用的工具。

轉載于:https://blog.51cto.com/fishparadise/1750787

總結

以上是生活随笔為你收集整理的BCP导出导入大容量数据实践的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。