BCP導出導入 SQL SERVER 大容量數據實踐教程

BCP是SYBASE公司提供專門用于數據庫表一級數據備份的工具。本文我們講講用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下執行。
BCP導出導入 SQL SERVER 大容量數據實踐教程

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:
BCP導出導入 SQL SERVER 大容量數據實踐教程

figure-2

簡單的導出例子2:
BCP導出導入 SQL SERVER 大容量數據實踐教程

figure-3

在SSMS上同時也可以執行:

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


code-1


BCP導出導入 SQL SERVER 大容量數據實踐教程

figure-4

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


code-2
BCP導出導入 SQL SERVER 大容量數據實踐教程


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即可,把數據導入。


BCP導出導入 SQL SERVER 大容量數據實踐教程


figure-6
BCP導出導入 SQL SERVER 大容量數據實踐教程


figure-7

使用BULK INSERT導入數據

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


code-5
BCP導出導入 SQL SERVER 大容量數據實踐教程

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

過程要花上幾分鍾的時間才能完成,請耐心等待一下。
使用上面介紹的用法導出數據:

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。
BCP導出導入 SQL SERVER 大容量數據實踐教程

figure-9
BCP導出導入 SQL SERVER 大容量數據實踐教程


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有權限讀取相關目錄和文件的權限。在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 編寫的。

更多相關文章
  • SQL Server附加數據庫失敗,錯誤5120問題解決辦法
    如果你碰到SQL Server附加數據庫失敗,錯誤5120問題的話可以進入來參考小編整理的一些問題解決辦法,有興趣的可進入參考.在SQL Server 上附加數據庫時收到錯誤提示無法打開物理文件XXX,操作系統錯誤5(拒絕訪問),SQL Server 錯誤5120解決辦法一(已驗證):登陸sqlse ...
  • 文章提供這款數據庫連接代碼是一款asp.net 與 Oracle,SQL Server,Access 數據庫類哦, 如果你是asp.net開發者那你就爽了啊,這是一款利用了.net可以與Oracle,SQL Serv
  • 我們在附加數據庫時出現這種錯誤原主要原因就是因爲我們目錄權限不夠了,只要簡單的給它有everyone權限就可以解決了.錯誤提示附加數據庫 對于 伺服器“PT-PCSQLEXPRESS”失敗. (Microsoft.S
  • 在orace數據庫中exp/imp命令是用來導入與導出數據庫了,下面我們來介紹oracle中 exp/imp導出導入數據庫例子,希望文章對各位有幫助.導出前先查看系統的NLS_LANG設置:echo $NLS_LANGAMERICAN_AMERICA.ZHS16CGB231280select use ...
  • 雖然很多朋友說ruby連接sqlserver在微軟官方有ODBC驅動了,但我還是出于自信自己要在于linux中直接配置了,下面給各位同學分享具體過程.因爲工作需要,要分析存放在SQL Server上的數據,所以不得不研究一下如何使用Ruby訪問SQL Server,發現其實還是很簡單的:安裝Free
  • Mysql命令行導出工具Mysqldump.exe默認存放在Mysql安裝目錄中的bin子目錄下,在使用Mysqldump導出數據庫時,首先請確保Mysql服務已啓動,下面我來介紹Mysqldump用法.Mysqld
  • 小編前面了介紹過另一種命令來實現數據庫導入導出了,下面小編再給各位介紹一篇oracle數據庫中expdp和impdp導出導入的例子,希望文章可以幫助到大家.expdp和impdp是oracle從10g開始新增加的導出.導入功能.名稱叫數據泵.expdp:Data Pump Exportimpdp:D
  • sql語句導入excel到db2數據庫sql語句導入excel到db2數據庫教程//方法一 import from "c:booknow.csv" of del messages "d:msg.out" insert
一周排行