有馬総一郎のブログ

(彼氏の事情)

Bcpにおけるテーブル列のスキップ その1 [XML形式でないフォーマットファイル、もしくはVIEWの使用]

SQL Serverの一括データコピー(bulk copy program)、bcpはOracleの SQL*Loader と比べる癖があるというか、痒いところに手が届かない感半端ない…

結論から言うと、テーブルの列(カラム)数に対して、インポートファイルの列(カラム)が少ない状態で一括データコピーしようとする時、フォーマットファイルは XML形式でない物を使う必要があり 、フォーマットファイルがXML形式の場合、後続の列(カラム)はスキップ出来るけれども、中間の列(カラム)に対しては、スキップできないようだ。

XML形式のフォーマットファイルを使って中間列(カラム)をスキップしてbcpを行うには、予めデータコピーする列(カラム)を絞った VIEW を作成して、それに対して行う必要がある。もしくはOPENROWSET(BULK...)関数を使う。

フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) | Microsoft Docsに書いてあるのだけど、流し読みするとあたかも出来ますよ、な体で書かれているんだよなぁ…(難癖かなぁ?でも、 ビューでの BULK INSERT の使用 に書くのおかしくない?もっと早い位置で書こうよ)

サンプルまんま、以下のようなテーブルがある。

1
2
3
4
5
6
CREATE TABLE myTestSkipCol
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );

上記テーブルmyTestSkipColに対して、列(カラム)Col2が足りてない、以下のインポートファイル myTestSkipCol2.dat がある。

myTestSkipCol2.dat
1
2
3
1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

列(カラム)Col2をスキップしてインポートしようとXML形式のフォーマットファイルを、以下のようにCol2の記述を<RECORD><ROW>から除去してIDSOURCEの番号を減らした形で作成する。

myTestSkipCol2.xml
1
2
3
4
5
6
7
8
9
10
11
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

でもってそのXML形式ファイルを使ってインポートを試みる。

XML形式フォーマットファイルを使用したテーブル列のスキップ失敗1

C:\Users\arimasou16>bcp myTestSkipCol in myTestSkipCol2.dat -E -f myTestSkipCol2.xml -S server -U user -d db -P password

コピーを開始しています...
SQLState = 23000, NativeError = 515
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]テーブル 'db.schema.myTestSkipCol' の列 'Col3' に値 NULL を挿入できません。この列では NULL 値が許可されていません。INSERT は失敗します。
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]ステートメントは終了されました。

何故かCol3にデータコピーが試みられずに失敗する。

sql - Bulk Insert with format file NOT skipping column in destination table with 146 fields as it should be - Stack Overflow

You could create a view of the relevant columns on tbl_person and insert to that.

Alternatively you could use an old-style non XML format file, or (perhaps easier, if your environment security setting allow it) use OPENROWSET(BULK…)

XML形式でない古いフォーマットファイルを使用したテーブル列のスキップ成功

繰り返しになるが、冒頭に書いたとおりXML形式でない古い形式のフォーマットファイルならば、列(カラム)Col2をスキップしてのbcpは成功する。

myTestSkipCol2.fmt
1
2
3
4
9.0
2
1       SQLCHAR       0       7       ","     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
C:\Users\arimasou16>bcp myTestSkipCol in myTestSkipCol2.dat -f myTestSkipCol2.fmt -S server -U user -d db -P password

コピーを開始しています...

3 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 16     平均 : (187.50 行/秒)

VIEWを使ったXML形式フォーマットファイルを使用したテーブル列のスキップ成功

もしくはCol1Col3とデータコピーする列(カラム)だけを定義した VIEW を作成して、それに対してbcpを行う。

1
2
3
CREATE VIEW v_myTestSkipCol AS
SELECT Col1,Col3
FROM myTestSkipCol;
C:\Users\arimasou16>bcp v_myTestSkipCol in myTestSkipCol2.dat -E -f myTestSkipCol2.xml -S server -U user -d db -P password

コピーを開始しています...

3 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 31     平均 : (96.77 行/秒)

長くなったので続く

Comments