大量匯入匯出的工具 BCP

by adonis 19. 十二月 2014 16:30

作    者:楊先民
審    稿:張智凱

 

前言

SQL Server 支援從 SQL Server 資料表匯出大量資料(也就是 bulk data),以及將大量資料匯入 SQL Server 資料表,本期就來介紹屬於大量資料匯入匯出的命令提示字元指令:BCP。

大量匯入匯出資料的方法


有多種方式可以進行大量資料的匯入與匯出,例如:
1.    BCP公用程式:可以大量匯入匯出資料並產生格式檔案的命令列公用程式 bcp.exe。
2.    Bulk insert的 T-SQL指令:T-SQL指令,可以直接將資料檔案匯入至資料庫資料表。
3.    INSERT SELECT * FROM OPENROWSET(BULK)陳述句。

本期的主題是單指BCP.EXE這個公用程式,所以其他兩個主題先不在我們的討論範圍。

通常類似 DTS或是 SSIS這種課程是很容易會把 BCP這個公用程式拿進來介紹,甚至連微軟自家的管理課程也是,不過 BCP這個公用程式並沒有進行「資料轉換」,它只負責做「資料搬移」,所以通常這類的主題我都沒有在管理的課程中,看完這篇文章是讓你對 BCP有個大略的了解。

 

格式檔案


bcp 公用程式、BULK INSERT 和 INSERT ... SELECT * FROM OPENROWSET(BULK...) 全都支援使用特殊的「格式檔案」(Format file),這是一個將每一個欄位的格式資訊儲存在資料檔中。 格式檔案也可以包含對應的 SQL Server 資料表的相關資訊。這樣當SQL Server大量匯出與匯入時,格式檔案可以提供所需的格式資訊,不過 bcp公用程式是唯一可以產生格式檔案的工具,所以我們必需要利用 bcp公用程式建立所謂的格式檔案。

建立格式檔案


如果你只是想大量匯 SQL Server的資料表到另一個 SQL Server資料表,那麼只要用 bcp這個公用程式即可,但是如果你想要匯到不同資料格式的話,你就需要建立所謂的「格式檔案」,而這個格式檔案只有 bcp這個公用程式可以建立,然後使用 bulk insert 或是 insert select * from openrowset(bulk)的指令來設定。
SQL Server 支援兩種類型的格式檔案:非 XML 格式和 XML 格式。 非 XML 格式是舊版 SQL Server 所支援的原始格式,所以我們使用 XML的格式吧(舊版就沒什麼好說的了)。

使用 bcp 命令建立格式檔案,需要指定 format 引數,並且使用 nul 取代資料檔的路徑。 format 選項則是需要 -f 選項;若要建立 XML 格式檔案,也要指定 -x 選項,例如:
bcp table_or_view format nul -f [格式檔案名稱] -x
當然,既然使用 XML格式,附檔名也最好使用 xml以供區別。

我們用微軟所附的範例資料庫為例,產生一個 xml的格式檔案如下:

bcp AdventureWorks.Person.Address format nul -c -x -f PersonAddress.xml -t, -T

-c是產生字元資料格式的格式檔案

-t這個引數是設定欄位的結束字元,預設是 \t的話則不用設定,以本例我們設定是逗號(,)

-T則是用 Windows驗證登入到 SQL Server,如果不設定的話,則需要使用 –U與 –P設定 SQL的帳號密碼。

執行好後,會在目錄中看到一個 xml 檔案,打開來如下:

image

這個就是xml的格式檔案。

如果你要用 –n的話,則是產生原生資料類型的格式檔案,結果如下:

image

你可以觀察看看,資料型別如果是選 –n的話,則格式檔案變成 NChar,所謂的 SQL Server資料型別,不然則是 CharTerm這種字元格式。
使用原生格式匯入匯出資料
我們先用 bcp匯入匯出資料從 SQL Server匯入到另一個 SQL Server,語法如下:

bcp AdventureWorks.Person.Address out C:\myAddress.Dat -n -T

當然,裡面只有一個 out是之前沒看過的,就是把它匯出成 *.dat的檔案,執行成功會變成這樣子:

image

我們就可以以此 dat 為基礎,匯資料到其他資料表了。

先產生一個空的資料表如下:
use tempdb
go

select * into Address from AdventureWorks.Person.Address
where AddressID = 0

再來就可以利用剛才 out的那個 dat將資料匯入到這個空的資料表中。

bcp tempdb..Address in c:\myAddressTest.dat -T -n

剛才匯出資料是用 out,而匯入資料則是用 in ,就是這兩個引數,還蠻簡單的吧?

image

最後匯入資料的結果,速度是相當的快。

適用 bcp的時機可以是這樣子的,你可以單獨的把資料表備份到 dat的 bcp檔案中,視情況最後快速將資料再匯入回資料庫中,或是很快速的產生出範例資料庫(事先寫好批次指令)。

LAST

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List