親/子レコードの挿入

ユースケース

レコードを挿入する際、親レコードに依存する子レコードに関する詳細を入力する必要がある状況は度々発生します。

例えばCRM システムを使う場合には、Invoices を入力するには最低1つの明細行が必要です。

Invoices には、各行が複数のフィールドを持つ多数の明細行を入力できます。こうしたInvoices の仕様は、データをリレーショナルテーブルとして提供する際に問題となります。

データを読み込む際、外部キーで接続されたInvoice とInvoiceLineItem テーブルをモデル化するのは容易です。
しかし挿入の際には、CRM システムはInvoice およびInvoiceLineItems を1つの操作で作成する必要があります。

こうした問題を解決するために、CData のツールは親コレクションカラム上に子コレクションカラムを提供します。
これらのカラムは、親および子レコード両方の情報を持つINSERT ステートメントを発行する際に使用することができます。

このページの例では、Invoice テーブルにはInvoiceLineItems というカラムが存在します。
挿入の際には、InvoiceLineItems テーブルに挿入する必要があるレコードの詳細を、Invoice レコードのInvoiceLineItems カラムに渡すことができます。
これは、以下の方法で可能です。

親および子レコードを挿入する方法

親および子レコードを挿入するために、本製品は2つの方法を用意しています。一時テーブルを使った挿入と、XML / JSON への集約による挿入です。

一時（#TEMP）テーブル

データを入力する最も簡易な方法は、本製品がメモリに格納する#TEMP テーブル、または一時テーブルを使用することです。

#TEMP テーブルは以下の構文で参照してください。

TableName#TEMP

#TEMP テーブルは接続中はメモリに格納されます。

そのため、これらのテーブルを使用する際には挿入の発行間で接続を閉じることができず、各クエリで異なる接続を使用する環境では一時テーブルは使用できません。

単一の接続内では、テーブルはバルク挿入が成功するまでメモリに残り、成功した時点でメモリから消去されます。

例：

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

InvoiceLineItems テーブルに書き込みが行われた後で、Invoice テーブルへの挿入中に#TEMP テーブルが参照されます。

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

内部では本製品が#TEMP テーブルから値を読み込みます。

ReferenceNumber は、どのInvoice に明細が紐付けられているかを特定するのに使用されています。
これは、異なるInvoice に異なる明細が記載されている状況で、#TEMP テーブルにバルク挿入で書き込みが行われ、使用される可能性があるためです。
これで#TEMP テーブルをバルク挿入で使用することができます。例：

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

この場合、2つの異なるInvoice を挿入しています。ReferenceNumber によって、どのLines がどのInvoice に入力されるかを決定します。

Note：ここで示したテーブルとカラムは、本製品が一般にどのように動作するかを示す一例です。特定のテーブルおよびカラム名は、本製品内で異なることがあります。

XML / JSON への集約

#TEMP テーブルの代替として、XML / JSON を直接使うこともできます。#TEMP テーブルはXML / JSON を作成するためには使われないので、同じ接続を使うか挿入後に接続を閉じるかは影響しません。

例：

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

または

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

以下の例ではReferenceNumber は使用していません。

これは、XML / JSON が仕様上参照によって渡されるものではなく、親レコードに対して挿入ごとに全体で渡されるためです。
行ごとに完全なXML / JSON を作成および発行する必要があるので、親レコードに子レコードを紐付け直すものは必要ありません。

その後、値を挿入します。

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

または

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Example for Reckon

For a working example of how temp tables can be used for bulk insert in Reckon, please see the following: