SQL Server 批次資料匯入匯出 (BCP, Bulk Insert & OPENROWSET)
2022-01-11
說明 SQL Server 在資料大量匯入、匯出上可以採取的三種工具：BCP、Bulk Insert 以及 OPENROWSET。
說明
BCP
BCP 是 CLI Tool，在安裝 SQL Server Management Studio 的時候隨附安裝，但也可以單獨安裝。
使用 ODBC 的方式讓 Client 端可以從 SQL Server 匯出資料，或者將資料匯入 SQL Server。
因為是 Client 端的執行程式，從 Client 端要將資料送入 SQL Server 時，僅需要 Clien 端具有資料檔案的讀取權限以及在 SQL Server 資料表上的寫入權限。
Export
BCP dbName.dbo.tableName out table.csv -c -t , -T -S sqlServerName| 參數 | 用途 |
|---|---|
| -T | Trusted Connection，使用目前的 Windows 登入進行資料庫連線 |
| -S | 指定連線的伺服器 |
| -t | 控制輸出、輸入檔案的分隔符號 |
| -c | Char Type |
| -w | Unicode Type |
Import
相較 Export 差別只是在於 out 換為 in。
BCP dbName.dbo.tableName in table.csv -c -t , -T -S sqlServerNameBulk Insert
Database Engine 的 TSQL 語法，匯入的檔案必須是 SQL Server Service 的執行帳號 (預設為 NT Service\MSSQLSERVER) 具有讀取權限才行。
所以一般是將檔案放置在 SQL Server 本機的 Drive，或者是透過 Shared Folder 的方式提供 SQL Server Serivce 讀取 (一般會將 SQL Server Service 執行帳號改為網域帳號的方式)。
BULK INSERT dbo.IISLog
FROM 'D:\Data\Documents\u_ex161214.log'
WITH (
CODEPAGE = 65001,
FIRSTROW = 2,
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)使用 SQL Server 匯入 IIS Logs 進行分析
OPENROWSET
持續編輯中 🧙♂️