利用 bcp 製作資料更新包

2017-06-28

3136
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。

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