SQL ServerのBCPコマンド

db

SQL ServerのBCPコマンドの覚書きです。テキストファイルを介してインポート／エクスポートしたい時などに使います。


BCP.exe（Bulk Copy Program:一括コピープログラム）

  • 構文
    • OUTはエクスポート（テーブルからファイルへ）
    • INはインポート（ファイルからテーブルへ）

bcp.exe データベース名.スキーマ名.テーブル名 INまたはOUT ファイル名 -オプション(複数指定可)

  • エクスポートの例(CultureテーブルをCulture.csvへエクスポート)
    • ファイルがなければ作成、あれば上書き

bcp.exe AdventureWorks.Production.Culture OUT C:\Culture.csv -c -S 192.168.1.1 -U sa

  • エクスポート出力されたファイルの例(列名は出力されない)

en English 1998-06-01 00:00:00.000
es Spanish 1998-06-01 00:00:00.000
fr French 1998-06-01 00:00:00.000

  • エクスポート時のオプション

-c･･･ASCIIデータで出力
-S･･･接続サーバ（IPまたは名前）、接続サーバ\インスタンス名
-U･･･ログインID
-P･･･パスワード（未指定時はプロンプト表示）
-T･･･-Windowsログインユーザでの信頼関係接続（-Uの代わりに利用）
-t･･･セパレータ（既定ではタブ区切り）

  • インポートの例(CultureAdd.csvをCultureテーブルへインポート)
    • データは追加(INSERT)となります
    • 事前にテーブルを作成しておく必要があります
    • 既定では、空白値の場合、NULLではなくDefault値が設定されます
    • 既定では制約／トリガは実行されません

bcp.exe AdventureWorks.Production.Culture IN C:\CultureAdd.csv -c -S 192.168.1.1 -U sa

  • インポートファイルの例（CultureAdd.csv）

ja Japanese 2008-08-01 00:00:00.000
it Italy 2008-08-01 00:00:00.000

  • インポート時のオプション

-E･･･ID列で自動採番せずに、CSVファイルのID値を使用
-k･･･空白の場合、Default値ではなくNULLを挿入


BCPコマンドは昔に比べて使う機会は減ったのですが、それでも時々、テスト用にパッとデータを抜きたいときや、入れたい時に使っている気がします。


  • おまけ1(全テーブルのエクスポートコマンドを生成するスクリプト)
------------------------------------------------------------
-- ■エクスポート パラメータ
------------------------------------------------------------
-- ▼(1)エクスポート元サーバ名(\インスタンス名)
DECLARE @EXP_SERVER VARCHAR(MAX)
SET @EXP_SERVER = 'SQLSERVERのIP'

-- ▼(2)エクスポートするサーバへのログイン方法
DECLARE @EXP_LOGIN VARCHAR(MAX)
SET @EXP_LOGIN = '-U sa -P パスワード'  /*SQL Server認証の場合*/
--SET @EXP_LOGIN = '-T' /*Windows認証の場合*/

-- ▼(3)CSV保存フォルダ(最後に\必要)
DECLARE @CSV_DIR VARCHAR(MAX)
SET @CSV_DIR = 'C:\Csv\'

-- ▼(4)列を区切るセパレータ文字
DECLARE @SEPARATE_CHAR VARCHAR(MAX)
SET @SEPARATE_CHAR = '\t' /*タブ文字*/

------------------------------------------------------------
-- ■エクスポートコマンドを生成するクエリ
--  (エクスポートするサーバのデータベースに接続してから実行)
------------------------------------------------------------
SELECT
	'bcp.exe ' + DB_NAME() + '.dbo.' + name + ' OUT ' + @CSV_DIR  + name + '.csv -c -t ' + @SEPARATE_CHAR + ' -S ' +  @EXP_SERVER + ' ' +  @EXP_LOGIN AS コマンド
FROM sys.objects  /*システムカタログ*/
WHERE
	type = 'U'  /*ユーザ作成テーブル*/
ORDER BY name
  • おまけ2(全テーブルのインポートコマンドを生成するスクリプト)
------------------------------------------------------------
-- ■インポート パラメータ
------------------------------------------------------------
-- ▼(1)インポート先サーバ名(\インスタンス名)
DECLARE @IMP_SERVER VARCHAR(MAX)
SET @IMP_SERVER = '(local)\SQLEXPRESS'

-- ▼(2)インポートするサーバへのログイン方法
DECLARE @IMP_LOGIN VARCHAR(MAX)
--SET @IMP_LOGIN = '-U sa -P パスワード'  /*SQL Server認証の場合*/
SET @IMP_LOGIN = '-T' /*Windows認証の場合*/

-- ▼(3)インポート先データベース名
DECLARE @IMP_DB_NAME VARCHAR(MAX)
SET @IMP_DB_NAME = 'MyDB'

-- ▼(4)CSV保存フォルダ(最後に\必要)
DECLARE @CSV_DIR VARCHAR(MAX)
SET @CSV_DIR = 'C:\Csv\'

-- ▼(5)列を区切るセパレータ文字
DECLARE @SEPARATE_CHAR VARCHAR(MAX)
SET @SEPARATE_CHAR = '\t' /*タブ文字*/

------------------------------------------------------------
-- ■インポートコマンドを生成するクエリ
--  (エクスポートするサーバのデータベースに接続してから実行)
------------------------------------------------------------
SELECT
	'bcp.exe ' + @IMP_DB_NAME + '.dbo.' + name + ' IN ' + @CSV_DIR  + name + '.csv -c -t ' + @SEPARATE_CHAR + ' -S ' +  @IMP_SERVER + ' ' +  @IMP_LOGIN + ' -E' AS コマンド
FROM sys.objects  /*システムカタログ*/
WHERE
	type = 'U'  /*ユーザ作成テーブル*/
ORDER BY name