SQL ServerのBCPコマンド
SQL ServerのBCPコマンドの覚書きです。テキストファイルを介してインポート/エクスポートしたい時などに使います。
BCP.exe(Bulk Copy Program:一括コピープログラム)
- 概要
- コマンドプロンプトから利用するSQL Serverのツール
- C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe
- 構文
- OUTはエクスポート(テーブルからファイルへ)
- INはインポート(ファイルからテーブルへ)
- エクスポートの例(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