親/子レコードの挿入

ユースケース

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

例えばCRM システムを使う場合には、Invoices を入力するには最低1つの明細行が必要です。請求書の行項目は複数のフィールドを持つことができるため、こうしたInvoices の仕様は、データをリレーショナルテーブルとして提供する際に問題となります。

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

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

例えば、Invoice テーブルにInvoiceLineItems というシングルカラムがあるとします。挿入の際には、InvoiceLineItems テーブルに挿入する必要があるレコードの詳細を、Invoice レコードのInvoiceLineItems カラムに渡すことができます。

次のセクションでは、この方法を説明します。

親 / 子レコードを挿入する方法

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

一時（#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の直接挿入

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

例：

[
  {
    "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>

XML はその性質上、親レコードに対して挿入ごとに全体で渡されるため、これらの例ではReferenceNumber が存在しないことに注意してください。行ごとに完全なXML を作成および発行する必要があるので、親レコードに子レコードを紐付け直すものは必要ありません。

次に、値を挿入します。

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

または

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

Example for NetSuite

The following example uses Aggregate Columns. All columns in the SuiteTalk schema that are suffixed with the word "Aggregate" identifies a column that represent a colleciton of objects. The AggregateColumnMode connection property must be set to a minimum of List in order for them to show up and be availalbe for inserts. The default for AggregateColumnMode is Ignore.

NetSuite only accepts XML aggregates

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

// Insert into JournalEntry_LineList child table
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPY','1','SINC191114T875102','7','2019-11-14', 'ACC1113Y', '150019', '0', 'INC1411437', 'a');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPY','1','SINC191114T875102','7','2019-11-14', 'ACC1113Y', '0', '150019', 'INC1411437', 'a');

INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPQ','2','SINC191114T875102','7','2019-11-15', 'ACC1114Y', '190019', '0', 'INC1411439', 'b');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPQ','2','SINC191114T875102','7','2019-11-15', 'ACC1114Y', '0', '190019', 'INC1411439', 'b');

INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPS','3','SINC191114T875103','8','2019-11-19', 'ACC1115Y', '110019', '0', 'INC1411441', 'c');
INSERT INTO JournalEntry_LineList#TEMP (Currency_Name,ExchangeRate,ExternalId,Subsidiary_InternalId,TranDate, LineList_Account_ExternalId, LineList_Debit, LineList_Credit, LineList_Entity_ExternalId, LineList_Sbt_Memo) VALUES ('JPS','3','SINC191114T875103','8','2019-11-19', 'ACC1115Y', '0', '110019', 'INC1411441', 'c');


// Insert into JournalEntry parent table
INSERT INTO JournalEntry#TEMP (ExternalId, LineListAggregate) VALUES ('SINC191114T875102', 'JournalEntry_LineList#TEMP');
INSERT INTO JournalEntry#TEMP (ExternalId, LineListAggregate) VALUES ('SINC191114T875103', 'JournalEntry_LineList#TEMP');


// Execute the bulk insert
INSERT INTO JournalEntry (ExternalId, LineListAggregate) SELECT ExternalId, LineListAggregate FROM JournalEntry#TEMP

For an example of how to insert item lists into invoices please see the following.