利用 bcp 製作資料更新包

  • 3082
  • 0
  • 2017-07-01

讓非程式人員幫忙執行資料更新,很多時候可以避免程式人員來回奔波,但前提是沒有操作錯誤。

這次打算以北風資料庫為例,利用 bcp 匯入資料,並將指令包裝,一個滑鼠點擊就能完成所有步驟。

一個維運中系統,如何在資料庫更動時,減少人為失誤?讓更新的手動步驟只留下 "啟動" ,也是一種辦法。

以下是以北風資料庫為例,進行資料的匯出及匯入,並將操作包裝到 bat 檔,使在執行任務時,只需一個動作,減少失誤的可能。

 

[指令匯出 bcp file]

匯出指令格式為:bcp <資料庫資料表> out <匯出檔名> <格式> -S <伺服器名稱> <驗證>

例如我用的是 LocalDB 的北風資料庫,要匯出 Orders 資料表,匯出檔名為 OrdersData.dat,驗證方式為 Windows 驗證,指令就會如下:

bcp Northwind.dbo.Orders out OrdersData.dat -n -S (LocalDb)\MSSQLLocalDB -T

-n 是匯出原生格式,內容不具可讀性。可以改用 -c,使輸出內容為字符,欄位間分隔符預設為 tab,若希望匯出 csv 則可以用 -c -t, 。

bcp Northwind.dbo.Orders out OrdersData.dat -c -t, -S (LocalDb)\MSSQLLocalDB -T

-T 指的是 Windows 驗證,有足夠權限的 Windows 登入者可用這個參數。用 SQL 帳號執行則可使用 -U<user name> -P<password> 。(帳密和參數沒有空隔)

bcp Northwind.dbo.Orders out OrdersData.dat -c -t, -S 192.168.0.53\SQLAD2008 -Unorthwinduser -P12345

 

[指令匯入 bcp file]

匯入指令格式為:bcp <資料庫資料表> in <匯入檔名> <格式> -S <伺服器名稱> <驗證>

例如我要將原生格式的 bcp 檔匯入到樣本資料庫的資料表,指令如下:

bcp SAMPLE_TEST.dbo.Orders in OrdersData.dat -n -S (LocalDb)\MSSQLLocalDB -T

比較需要注意的是,匯入檔的格式要打對,例如當初是用 -c -t, 匯出 csv ,也要在匯入時指明 -c -t,。

 

[搭配 sqlcmd 更動 table]

執行 T-SQL 的指令格式為:sqlcmd -S <伺服器名稱> -d<資料庫> -Q "<T-SQL 指令>"

例如我需要在匯入 bcp 檔之前,先把資料表清空,指令如下:

sqlcmd -S (LocalDb)\MSSQLLocalDB -d SAMPLE_TEST -Q "truncate table Orders"

但 T-SQL 如果再長一點,還是放在 sql 檔中,比較好維護,例如 truncate-table.sql,這時可以改下指令:

sqlcmd -S (LocalDb)\MSSQLLocalDB -d SAMPLE_TEST -i truncate-table.sql

 

[batch file 製作]

之要把執行的指令預先存在 bat 檔中,就可以照順序一次執行,手誤的風險也會降低。

新增一個 txt 純文字檔把指令寫好後,再改變副檔名為 bat ,就完成一個可執行的 batch file。

如果我希望可以依序執行「資料表清空」、「匯入 bcp」,batch file 的內容可以寫作:

sqlcmd -S (LocalDb)\MSSQLLocalDB -d SAMPLE_TEST -i truncate-table.sql
bcp SAMPLE_TEST.dbo.Orders in OrdersData.dat -n -S (LocalDb)\MSSQLLocalDB -T
pause>nul

puase>nul 是希望執行完命令視窗可以留著,好確認程式已執行,檔案結構及執行畫面如下:

[讓 batch file 只執行一次]

如果製作這份更新包目的是為了可以方便讓他人代為執行,可能就要加一點防呆了,例如只能執行一次。

@echo off
IF EXIST excuted.txt (
    echo this update pakage has been exucted, do not again.
    pause>nul
    exit
)

sqlcmd -S (LocalDb)\MSSQLLocalDB -d SAMPLE_TEST -i truncate-table.sql
bcp SAMPLE_TEST.dbo.Orders in OrdersData.dat -n -S (LocalDb)\MSSQLLocalDB -T
copy nul excuted.txt /y
echo done.

pause>nul

@echo off 可以關掉指令產生的 console,這樣我要提示的 echo 就不會被埋在一堆 console 裡了。

echo 主要是顯示訊息,像是重複執行或做完了。

這裡利用 excuted.txt 是否存在來判斷是否已執行過,若未執行則產生一個 excuted.txt。

執行結果及檔案結構如下圖:

[參考資料]

[1]Working with the bcp Command-line Utility https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

[2]How to use the Bulk Copy Process (BCP) to export Microsoft Dynamics GP data from one database and import data into a new database https://support.microsoft.com/zh-tw/help/875179/how-to-use-the-bulk-copy-process-bcp-to-export-microsoft-dynamics-gp-d

[3]使用 sqlcmd 公用程式 (SQL Server Express) https://technet.microsoft.com/zh-tw/library/ms165702(v=sql.105).aspx