Inserting Parent and Child Records
Use Case
Sometimes, when inserting records, it's necessary to supply details about child records that have a dependency on a parent.
For example, 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
The add-in facilitates two methods for inserting parent/child records: temporary table insertion and XML aggregate insertion.Temporary (#TEMP) tables
The simplest way to enter data would be to use a #TEMP table, or temporary table, which the add-in 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 add-in 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 add-in works in general. The specific table and column names may be different in the add-in.
Direct XML Insertion
Direct XML can be used as an alternative to #TEMP tables. Since #TEMP tables are not used to construct them, it does not matter if you use the same connection or close the connection after insert.
For example:
[
{
"Item", "Basketball",
"Quantity": 10
"Amount": 9.99
},
{
"Item", "Football",
"Quantity": 5
"Amount": 12.99
}
]
OR
<Row> <Item>Basketball</Item> <Quantity>10</Quantity> <Amount>9.99</Amount> </Row> <Row> <Item>Football</Item> <Quantity>5</Quantity> <Amount>12.99</Amount> </Row>
Note that the ReferenceNumber is not present in these examples because the XML, by its nature, is passed against the parent record in full per insert. Since the complete XML must be constructed and submitted for each row, there is no need to provide something to tie the child back to the parent.
Now insert the values:
INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')
OR
INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')
Note: The add-in also supports the use of XML/JSON aggregates.
Executing Deep Inserts with SQL
The add-in supports OData deep inserts, in which you simultaneously create a base entity and link it to related entities, by specifying navigation properties. To specify Navigation Properties for an entity, you may either submit JSON / XML data, or you may create a temporary table for the navigation property and then reference the temporary table in the insert to the base table. Submit the XML / JSON or reference the temporary table in the appropriate navigation property column on the base table. Each navigation property column is prefixed with the word "Linked".
Example: Deep Inserts using XML / JSON
To submit XML or JSON data, simply supply the values for the table the navigation property is referencing in XML or JSON format. If you are familiar with the OData standard, you should not be submitting values in the standard. The XML / JSON used here is simply a means of supplying multiple values ot the CData Excel Add-In for OData.
For example, consider the Orders table in Northwind odata.org test service. To create a new Order, you specify the Products ordered, Customer, Employee, and Shipper. To do so, you need to specify the Customer, Order_Details, Shipper, and Employee navigation properties.
- Customer: The following XML represents a new Customer:
<Row> <CustomerID>VINET</CustomerID> <CompanyName>Vins et alcools Chevalier</CompanyName> <ContactName>Paul Henriot</ContactName> <ContactTitle>Accounting Manager</ContactTitle> <Address>59 rue de l'Abbaye</Address> <City>Reims</City> <PostalCode>51100</PostalCode> <Country>France</Country> <Phone>26.47.15.10</Phone> <Fax>26.47.15.11</Fax> </Row> - Order_Details: The following JSON add two Products to the Order:
[ { "ProductID": 72, "UnitPrice": 34.80, "Quantity": 5, "Discount": 0 }, { "ProductID": 42, "ProductID": 9.80, "ProductID": 10, "ProductID": 0 } ] - Employee: The following XML specifies the existing Employee:
<Row> <EmployeeID>5</EmployeeID> </Row> - Shipper: The following JSON specifies the existing Shipper:
[ { "ShipperID": 3 } ]
In order to execute the insert, simply reference or include as string literals the complete XML / JSON. For example:
INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', '{ ... }', '<Row>...</Row>', ?, ?)
Example: Deep Inserts using Temporary Tables
If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. Keeping with the Northwind example, you need to specify the following navigation properties.
Creating Temporary Tables
Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.
- Customer: The following statement creates a new Customer:
INSERT INTO Customers#TEMP (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone, Fax) VALUES ('VINET', 'Vins et alcools Chevalier', 'Paul Henriot', 'Accounting Manager', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '26.47.15.10', '26.47.15.11') - Order Details: The following statements add two Products to the Order:
INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (72, 34.80, 5, 0) INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (42, 9.80, 10, 0)
- Employee: The following statement specifies the existing Employee:
INSERT INTO Employees#TEMP (EmployeeID) VALUES (5)
- Shipper: The following statement specifies the existing Shipper:
INSERT INTO Shippers#TEMP (ShipperID) VALUES (3)
The CData Excel Add-In for OData will assume that the Shipper and Employee already exist and will only link to the existing references since only the primary keys were specified for either. When more than just the primary key is defined, such as the examples for Customer and Order_Details, the CData Excel Add-In for OData will attempt to create new entries - triggering the deep insert.
Inserting the Entity
In the INSERT statement for the base entity, reference the temporary tables in the LinkedOrder_Details, LinkedCustomer, LinkedEmployee, and LinkedShipper columns:
INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', 'Order_Details#TEMP', 'Customers#TEMP', 'Employees#TEMP', 'Shippers#TEMP')
Example: Bulk Inserts using Temporary Tables
If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. Use IncludeReferenceColumn connection property to add a input only Reference column to properly associate children during a deep insert with the same parent. Keeping with the Northwind example, you need to specify the following navigation properties.
Creating Temporary Tables
Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.
- Categories: The following statements adds to Categories child table:
INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 4, 'DVD') INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 5, 'BluRay') INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (200, 6, 'Radio')
- Products: The following statements adds to Product parent table:
INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (20, 100, 45, 'Categories#TEMP') INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (21, 200, 25, 'Categories#TEMP')
Inserting the Entity
In the INSERT statement for the base entity, reference the temporary table in the LinkedCategories column:
INSERT INTO Products (ID, ParentReference, price, LinkedCategories) SELECT ID, ParentReference, price, CategoriesAggregate FROM Products#TEMP