SQL Serverの一括データコピー(bulk copy program)、bcpはOracleの SQL*Loader と比べる癖があるというか、痒いところに手が届かない感半端ない…
結論から言うと、テーブルの列(カラム)数に対して、インポートファイルの列(カラム)が少ない状態で一括データコピーしようとする時、フォーマットファイルは XML形式でない物を使う必要があり 、フォーマットファイルがXML形式の場合、後続の列(カラム)はスキップ出来るけれども、中間の列(カラム)に対しては、スキップできないようだ。
XML形式のフォーマットファイルを使って中間列(カラム)をスキップしてbcpを行うには、予めデータコピーする列(カラム)を絞った VIEW を作成して、それに対して行う必要がある。もしくはOPENROWSET(BULK...)関数を使う。
フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) | Microsoft Docsに書いてあるのだけど、流し読みするとあたかも出来ますよ、な体で書かれているんだよなぁ…(難癖かなぁ？でも、 ビューでの BULK INSERT の使用 に書くのおかしくない？もっと早い位置で書こうよ)
サンプルまんま、以下のようなテーブルがある。
CREATE TABLE myTestSkipCol
(
Col1 smallint,
Col2 nvarchar(50) NULL,
Col3 nvarchar(50) not NULL
);
上記テーブルmyTestSkipColに対して、列(カラム)Col2が足りてない、以下のインポートファイル myTestSkipCol2.dat がある。
myTestSkipCol2.dat
1,DataForColumn3
1,DataForColumn3
1,DataForColumn3
列(カラム)Col2をスキップしてインポートしようとXML形式のフォーマットファイルを、以下のようにCol2の記述を<RECORD>と<ROW>から除去してIDやSOURCEの番号を減らした形で作成する。
myTestSkipCol2.xml
<?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にデータコピーが試みられずに失敗する。
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
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形式フォーマットファイルを使用したテーブル列のスキップ成功
もしくはCol1、Col3とデータコピーする列(カラム)だけを定義した VIEW を作成して、それに対してbcpを行う。
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 行/秒)
長くなったので 続く