Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Inserting into Multiple Parent-Child SQL Tables in a Single Stored Procedure Call using the BizTalk Server WCF-SQL Adapter

This post covers how to load records into several tables that have parent-child relationships, via a single stored procedure call and the use of SQL user-defined table types. You may have seen how to use table types for BizTalk SQL loads before (such as in this post), but the approach detailed below shows how to load multiple parent and child records within the same stored procedure, while also executing no more than one insert statement for each table being loaded. (Note that user-defined table types is only available in SQL Server 2008 or later).

Having BizTalk make just one SQL stored procedure call for all records has multiple benefits:

  • Reduces the number of BizTalk messages going through the BizTalkMsgBoxDb database, as well as the number of roundtrips between BizTalk and SQL Servers, reducing overhead and improving speed of loads
  • All record loads can be performed in the same transaction
  • No need to de-batch and create separate messages (for each root parent record) within BizTalk

In addition, the stored procedure calls just one insert for each table (avoiding having to loop through records), resulting in significant performance gains.

Suppose you need to load the following Order tables:


Now also suppose that for the input, your BizTalk solution will receive an Order XML file containing one or more orders.


In the above CanonOrders schema, there can be one or more Order nodes. Each Order can have one Header, multiple Addresses, multiple Details, and each Detail can have multiple Notes.


Step 1: Create SQL Table types

First, we need to create the SQL table types. When creating these, you only need to include the fields that you’ll be passing into the stored procedure. For example, fields such as CreatedDate or ModifiedDate are excluded here, as those fields will be set within the stored procedure.


Also, in the place of Primary and Foreign key Identity columns, set ‘Temporary Id’ columns. For example, in the OrderDetail table, ‘OrderDetail_TempId (varchar(20) takes the place of the OrderDetailId (bigint) Identify column, and ‘OrderHeader_TempId (varchar(20) takes the place of the OrderHeaderId (bigint) foreign key.

There’s no reason to include the identity columns, because we won’t know the identity values until after the records have been loaded into the tables. And the temporary Id columns allows us to link parent and child records together when inserting into the tables later.

Note: Here we’re using varchar(20) for the TempIds because later we’ll be mapping alphanumeric values to it, and the length of 20 will cover our range of values. But know that the datatype doesn’t need to be limited to varchar (it can be a numeric type such as int or bigint, depending on how you later map to it).



Step 2: Create the stored procedure

We need to create the stored procedure next.


Declare ‘IDMapping’ cross reference(xref) table variables, which will be used to lookup the actual Identity value tied to a temp Identity value. We only need to create these xref tables for parent tables that have child tables. (For this Order example, we only need ones for OrderHeader and OrderDetail).


Next we insert into each table, starting with the root parent, and drilling down from there. If we’re inserting into a parent table, we’ll also be using SQL’s OUTPUT clause to capture the newly inserted Identity value, as well as the corresponding temp id, in our ‘IdMapping’ xref table.


Inserting into Order Header


You’ll see that we’re using a Merge statement. This is done to get around a limitation of the OUTPUT clause, allowing us to select a column from the source table in the OUTPUT clause . In this case, it allows us to select the OrderHeader_TempId field from the @OrderHeader tabletype parameter. This field needs to be loaded into the @OrderHeaderIdMapping xref table variable, along with the actual inserted Identity, for foreign key lookups later in the procedure.


Inserting into OrderAddress


This table doesn’t have any child tables, so we don’t need to output the OrderAddressId identity values. It’s a more straightforward insert. But we do need to join with @OrderHeaderIdMapping to look up the OrderHeaderId foreign key value (based on @OrderAddress.OrderHeader_TempId).


Inserting into OrderDetail


Just as we did in the OrderHeader insert, we need to output the inserted identity values along with the corresponding temp id. For OrderDetail we insert these values into @OrderDetailMapping, so that we can lookup OrderDetailId later when inserting into OrderDetailNote. In addition, when specifying the source fields in the MERGE statement, we need to join with @OrderHeaderIdMapping to look up the OrderHeaderId foreign key value (based on @OrderDetail.OrderHeader_TempId).


Inserting into OrderDetailNote


This table doesn’t have any child tables, so we don’t need to output the OrderDetailNoteId identity values. But we do need to join with @OrderDetailIdMapping to look up the OrderDetailId foreign key value (based on @OrderDetailNote.OrderDetail_TempId).


Step 3: Generate BizTalk SQL Schemas

To generate the SQL schemas for the uspInsertOrders stored procedure…

  • In Visual Studio, right-click the BizTalk (schemas) project, and select Add –> Generated Items…
  • In the ‘add Generated Items’ window, select Consumer Adapter Service


  • In the Consumer Adapter Service Window..
    • Select ‘sqlBinding’, and configure your SQL connection string URI
    • Click Connect
    • Select ‘Client (Outbound operations)’ as the contract type
    • Click on Strongly-typed Procedures, select the stored procedure under ‘Available categories and operations’ (in the case below ‘dbo.uspInsertOrders’), and click ‘Add’


Two schemas will be generated, one for the table types (InsertOrdersTableType.dbo.xsd), and one for the stored procedure call (InsertOrdersTypedProcedure.dbo.xsd). Additionally, a bindings file for a two way WCF_SQL send port is created. Before you import this into your application, you’ll want to edit the bindings to rename the Send Port and set the ‘ApplicationName’. I also renamed this to InsertOrders.bindinginfo.xml, and changed it from a two-way send Port, to a one-way Send Port.

Optionally, you can specify a Filename Prefix in the ‘Consume Adapter Service’ window. Otherwise, you’ll end up with schemas named TableType.dbo.xsd and TypedProcedure.dbo.xsd, which is not recommended, and can be problematic if you need to generate additional schemas for other stored procedure calls.


Step 4: Map to the SQL Stored Procedure Request schema

When mapping CanonOrders to the uspInsertOrders stored procedure call, set loops between the source nodes and the corresponding ‘Type’ Node.


Next we need to map the temporary Primary and Foreign Key Ids in the table types. In the mapping below, we create a separate mapping page called ‘Ids’ for readability.


In this Order mapping, the temporary Id value we’re using here is a combination of: <parent record temp id> + <identifier text> + <Iteration Id>. If we only used the Iteration Functoid value for, let’s say, the OrderAddress_TempId, then we’d end up with duplicate OrderAddress_TempId values. That’s because the Iteration Functoid would restart at 1 when iterating Addresses on the next Order record. By including the parent record’s temp id as a prefix, we’re ensuring all child TempIds are unique, across all parents.

But know you can use any value for the temp id, as long as it’s unique for each child record, and as long as the same value is used for ‘XXX_TempId’ Primary and Foreign keys.

Some examples of Temp Id values that would result from the above mapping:

  • First Order: OrderHeader_TempId value=HDR1
  • First Order, first OrderDetail: OrderDetail_TempId value=HDR1DTL1
  • First Order, first OrderDetail: OrderHeader_TempId (temp ‘foreign key’ id) value=HDR1
  • First Order, second OrderDetail: OrderDetail_TempId value=HDR1DTL2
  • Second Order, first OrderDetail: OrderDetail_TempId value=HDR2DTL1

Below is the String Concatenate functoid configuration for the OrderDetail_TempId value (where the Header temp Id is included as a prefix).


The following is an example map result, given an input CanonOrders.xml with two orders. The first Order has two Addresses, and the second Order has one Address.

Mapped OrderHeaderType records


Mapped OrderAddressType records



Step 5: Deploy and Test

Finally, we have all of the components needed to execute end to end. The example input order file here is rather small (with only two orders, and a couple of addresses and order details each).

Sample CanonOrder.xml inpuT



As you can see in the SQL table results below, all inserted records have proper foreign keys set for all child tables.


To recap, the basic steps to this approach:

  1. Create SQL table types for the tables being loaded. Include Temporary Id columns in place of primary and foreign key Identity key columns.
  2. Create the stored that takes in the table types as parameters and loads the data. This stored procedure will execute one insert for every table. While inserting into parent tables, identity values, along with the temp id values, will be outputted to an id xref table variable.
  3. Generate BizTalk schemas for the SQL procedure call and table type parameters.
  4. Map to the stored procedure, setting each table type record’s temp primary and foreign key ids (to maintain the link between parent and child records).
  5. Deploy schemas and map. Deploy the WCF-SQL Send Port and test

Alternatives to using Table Types

Of course, the use of table types is not required for this approach. If you’re loading into SQL Server 2005, table types are not an option. Instead, you can pass in XML as an argument to the stored procedure, and have SQL query the XML.

However, if given a choice I’d always recommend passing in table types over XML for a few reasons:

  • Passing in XML bloats the data, due to the XML tags.
  • The XML must be parsed within SQL Server, which is expensive. Table types are native to SQL and can be read/used immediately without any transformation.
  • You can enforce data/type integrity with table types. With XML, you can pass in bad data/incorrect types, and not know it until the XML is parsed within the stored procedure.


Learn more about Tallan or see us in person at one of our many Events!

Share this post:

5 Comments. Leave new

Jasper Defesche
September 21, 2016 6:05 am

thanks for the article. It helped me a lot. I did ran into an issue, however. If the source message doesn’t have a specific set of data (for instance an order without lines), the maps generates an empty table element, which is not accepted by the WCF-SQL adapter (see https://social.technet.microsoft.com/Forums/ru-RU/a1802dcd-0d62-4b33-acf6-eb8d3376c1a7/bug-in-wcf-sql-adapter-using-user-defined-table-types?forum=biztalkr2adapters).

This can be prevent by adding a couple of functoids to the map:
– concatenate functoid linking all source fields
– a cumulative concatenate functoid
– a string length functoid
– a greater than functoid which checks the length of 0 and is linked to the target table type node.

First. I used a logical existence functoid but that generated multiple table type parent nodes, which is invalid xml.

Hi Tom, I hope you still reviewing this post. I am trying to reproduce this example slightly different. In my case I am dealing with an EDI file that shouldn’t be an issue, and I am trying to insert 2 sibling elements to SQL server using an Stored Procedure passing Table Types. I am testing with 2 EDI elements that are siblings, but in the end only 1 passes with data to the SP, the other appears to be empty, even when it is present on the file. Any thoughts? Thanks in advance

Hi Jorge, the scenario you’re describing looks to be an issue with your map (the map from the EDI file to the schema representing the stored proc call with the table types). I would first verify that the output of the map contains both sibling records. If both records are being mapped to the sql schema, then next I would check the looping logic in the stored procedure. Let mem know if this helps.

I like your solution and i’m looking kind of this nut i have question for my solution as:
1) I have to load data from STaging table to another five tables, out of that one table i have to load data from staging table fields + FK column value from another PK1 table ID and other FK column value from another PK2 table ID.
2) Another table is a Master kind table that contains all tables PK values + Staging Fields.

Thanks for your help!

@poratipcs – It’s not clear to me what your question is. Also, my solution is focused on passing in data from BizTalk and loading it in an efficient manner. What you’re describing seems to be loading a set of tables from a staging table in the same database.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>