Creating Multi-level Insert Operations Using Autoincrement Primary Keys

When creating multi-level (chained) insert operations where the primary key of the parent MBO is set to autoincrement, use the "@@identity" parameter in the select statement to provide the chained insert value.

This method of creating a multilevel insert operation is useful if the primary key is set to autoincrement, you are making the relationship between two related Adaptive Server Enterprise/SQL Anywhere database mobile business objects, and you are creating them from the tool palette within the Mobile Application Diagram.

Create two MBOs (for example, Customer and Sales_order).
The Customer table has an "id" column which is a primary key of int type with autoincrement default values (or identify type). Sales_order is another table, which has a column named "cust_id" ( a foreign key of int type).
Since "id" is set to autoincrement, each new row added to the table is uniquely identified. ("id" becomes "@@identity" from the first SQL insert statement).
From the Mobile Application Diagram, define the relationship between the MBOs as a Composite and One to many, and link the Customer table's "id" attribute to Sales_order's "cust_id".
Be sure that the child MBO will be synchronized either independently, or through the parent MBO.
Use a create statement to insert into the Customer MBO and add a select statement that returns the "@@identity" row. The ID column returned via this select query serves as the ID used by the chained insert statement into the Sales_Order MBO:

Example: Chained insert SQL statement

INSERT INTO sampledb.dba.customer
(
fname,
lname,
address,
city,
state,
zip,
phone,
company_name)
VALUES
('["id"=":id"]',
'["fname"=:fname"]',
'["lname"=":lname"]',
'["address"=":address"]',
'["city"=":city"]',
'["state"=":state"]',
'["zip"=":zip"]',
'["phone"=":phone"]',
'["company_name"=":company_name"]'
)
SELECT * FROM sampledb.dba.customer WHERE id=@@IDENTITY

Note: "id" is a primary key column of identity(or autoincrement) type. Notice that the extra select statement and 'id' are not part of the insert statement itself.