【Delphi/400】FireDAC SQLで配列DMLを使って複数行を一括INSERTするテクニック

Delphi/400のSQLで複数行を一括追加したい場合、
例えば以下のようなINSERT文を記述・発行するかと思います。
（VALUESの後に各行の値をカンマでつなげる）

INSERT INTO FILEYB (YB0001, YB0002, YB0003, YB0004, YB0005) VALUES
('A', 25, '滋賀県', 'SHIGA', '大津市'),
('A', 26, '京都府', 'KYOTO', '京都市'),
('A', 27, '大阪府', 'OSAKA', '大阪市'),
('A', 28, '兵庫県', 'HYOGO', '神戸市'),
('A', 29, '奈良県', 'NARA', '奈良市'),
('A', 30, '和歌山県', 'WAKAYAMA', '和歌山市')

　
しかし数百行、数千行と一括で更新したい場合に
長大なSQLを発行しようとするとエラーの原因となってしまいます。

※FireDAC接続では約16000byteまでしかSQL文を設定できない制約がございます。
（BDE・dbExpress接続でも約32000byteまで）

　
数百～数千行単位でまとめて行追加するSQLを発行したい場合、
上記のような連接のINSERTのSQL文を都度作成して発行することがありますが、
ここでパラメータ（ParamByName）を使って回すことができれば
SQL生成やロジック修正の手間を節約することができます。

今回はSQLでパラメータ使用時に、Query（TFDQuery）を使って
複数行を一括INSERTするための手順をご紹介します。
　

---

　
今回は、FireDAC接続で利用できる配列DMLという更新方法をご紹介します。

パラメータを配列にして渡すことで、1回のSQL実行で複数登録を可能にする仕組みです。
通信回数を減らすことができ、処理効率化が可能になります。

Delphi/400では個別処理した場合と処理速度はほとんど変わりませんが、
SQLServerやOracleといったDB側で配列DMLをサポートしているものであれば、
さらに効率があがり処理速度の向上も期待できます。

また、通常のSQLにはパラメータ個数の上限（1040個まで）がありますが、
配列DMLにおける配列の要素数には上限はありません。
（※弊社環境では下記サンプルロジックで最大100万行まで検証）

　

ここでは主にTFDQueryが持つ以下のプロパティやメソッドを使用します。

• Params.ArraySize：パラメータの配列要素数を指定します。
• AsStrings・AsIntegers等：パラメータを配列として渡すのに使用します。
• Execute：更新SQL（配列DML）を実行します。

　
以下、配列DMLを使用した一括INSERTを行うサンプルです。

var
  i: Integer;
  sSQL: String;
begin
  // SQL文の指定
  sSQL := ' INSERT INTO FILEYC ' +
          ' (YC0001, YC0002, YC0003 ' +
          ' ) VALUES ( ' +
          ' :YC0001, :YC0002, :YC0003) ';
  FDQuery1.SQL.Text := sSQL;

  // パラメータの配列要素数（実行回数）を設定
  FDQuery1.Params.ArraySize := 1000;

  // 各配列要素数に対してパラメータの値を設定
  for i := 0 to (FDQuery1.Params.ArraySize - 1) do
  begin
    FDQuery1.ParamByName('YC0001').AsStrings[i]  := 'XXTEST';
    FDQuery1.ParamByName('YC0002').AsIntegers[i] := StrToInt(FormatDateTime('YYMMDD', Date));
    FDQuery1.ParamByName('YC0003').AsStrings[i]  := 'TEST' + FormatFloat('00000', i);
  end;

  // SQL実行（Executeを使用）
  FDQuery1.Execute(FDQuery1.Params.ArraySize);
end;

　

　

　

＜発展＞

配列DMLを利用して、INSERT以外のSQLも発行可能です。
（下記は先ほどINSERTしたレコードに対して一括UPDATEを行うサンプルです）

注意事項としては、更新フィールドやWHERE句を含めて
すべてのパラメータを配列にする必要があります。
（一部だけAsStringなど配列でないパラメータにするとエラーになります）

var
  i: Integer;
  sSQL: String;
begin
  // SQL文の指定
  sSQL := ' UPDATE FILEYC SET  YC0004 = :YC0004 ' +
          ' WHERE (YC0001 = :YC0001) AND (YC0002 = :YC0002) AND (YC0003 = :YC0003) ';
  FDQuery1.SQL.Text := sSQL;
  // パラメータの配列要素数（実行回数）を設定
  FDQuery1.Params.ArraySize := 1000;
  // 各配列要素数に対してパラメータの値を設定
  for i := 0 to (FDQuery1.Params.ArraySize - 1) do
  begin
    FDQuery1.ParamByName('YC0001').AsStrings[i]  := 'XXTEST';
    FDQuery1.ParamByName('YC0002').AsIntegers[i] := StrToInt(FormatDateTime('YYMMDD', Date));
    FDQuery1.ParamByName('YC0003').AsStrings[i]  := 'TEST' + FormatFloat('0000000', i);
    FDQuery1.ParamByName('YC0004').AsStrings[i]  := 'TEST_' + FormatDateTime('HH.NN.SS', Now);
  end;
  // SQL実行（Executeを使用）
  FDQuery1.Execute(FDQuery1.Params.ArraySize);
end;