Use Case

When inserting records, often there is a need to fill in details about child records that have a dependency on a parent.

For instance, when dealing with a CRM system, Invoices often cannot be entered without at least one line item. Since invoice line items can have several fields, this presents a unique challenge when offering the data as relational tables.

When reading the data, it is easy enough to model an Invoice and an InvoiceLineItem table with a foreign key connecting the two. However, during inserts, the CRM system requires both the Invoice and the InvoiceLineItems to be created in a single submission.

To solve this sort of problem, our tools offer child collection columns on the parent. These columns can be used to submit insert statements that include details of both the parent and the child records.

For example, let's say that the Invoice table contains a single column called InvoiceLineItems. During the insert, we can pass the details of the records that must be inserted to the InvoiceLineItems table into Invoice record's InvoiceLineItems column.

The following subsection describes how this might be done.

Methods for Inserting Parent/Child Records

Temporary (#TEMP) tables

The simplest way to enter data would be to use a #TEMP table, or temporary table, which the provider will store in memory.

Reference the #TEMP table with the following syntax:

TableName#TEMP

#TEMP tables are stored in memory for the duration of a connection.

Therefore, in order to use them, you cannot close the connection between submitting inserts to them, and they cannot be used in environments where a different connection may be used for each query.

Within that single connection, the table remains in memory until the bulk insert is successful, at which point the temporary table will be wiped from memory.

For example:

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)

Once the InvoiceLineItems table is populated, the #TEMP table may be referenced during an insert into the Invoice table:

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

Under the hood, the provider will read in values from the #TEMP table.

Notice that the ReferenceNumber was used to identify what Invoice the lines are tied to. This is because the #TEMP table may be populated and used with a bulk insert, where there are separate lines for each invoice. This enables the #TEMP tables to be used with a bulk insert. For example:

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

In this case, we are inserting two different Invoices. The ReferenceNumber is how we determine which Lines go with which Invoice.

Note: The tables and columns presented here are an example of how the provider works in general. The specific table and column names may be different in the provider.

Example for WooCommerce

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

// Insert into Products table
INSERT INTO Products#TEMP (Name) values ('test010')
INSERT INTO Products#TEMP (Name) values ('test110')

Insert INTO Products (Name) SELECT Name FROM Products#TEMP


// Insert into Products table
INSERT into ProductCategories#TEMP (Id,Name) values (17,'Uncategorized')

INSERT INTO Products#TEMP (Name,CategoriesAggregate) values ('name1',ProductCategories#TEMP)

Insert into Products (Name,CategoriesAggregate) SELECT Name,CategoriesAggregate from Products#TEMP


// Insert into Products table
INSERT into ProductCategories#TEMP (ReferenceNumber,Id,Name) values (1,17,'Uncategorized')
INSERT into ProductCategories#TEMP (ReferenceNumber,Id,Name) values (2,18,'Categorized')

INSERT INTO Products#TEMP (ReferenceNumber,Name,CategoriesAggregate) values (1,'name1',ProductCategories#TEMP)
INSERT INTO Products#TEMP (ReferenceNumber,Name,CategoriesAggregate) values (2,'name2',ProductCategories#TEMP)

Insert into Products (ReferenceNumber,Name,CategoriesAggregate) SELECT ReferenceNumber,Name,CategoriesAggregate from Products#TEMP

//Update Products table
INSERT INTO Products#TEMP (Id,Name) values (264,'test010')
INSERT INTO Products#TEMP (Id,Name) values (265,'test020')

Update Products (Id,Name) SELECT Id,Name from Products#TEMP

// Delete from Products Table
INSERT INTO Products#TEMP (Id) values (264)
INSERT INTO Products#TEMP (Id) values (265)

Delete from Products Where exists SELECT Id from Products#TEMP