SQL Server 批次資料匯入匯出 (BCP, Bulk Insert & OPENROWSET)


  1. 說明
    1. BCP
      1. Export
      2. Import
    2. Bulk Insert
    3. OPENROWSET
  2. 參考資料
  3. 相關連結

說明 SQL Server 在資料大量匯入、匯出上可以採取的三種工具:BCP、Bulk Insert 以及 OPENROWSET。

SQL Server Logo

說明

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 sqlServerName

Bulk 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

持續編輯中 🧙‍♂️

參考資料

bcp 公用程式

使用 bcp 匯入和匯出大量資料 (SQL Server)

VITO の 學習筆記: 匯入匯出

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記