Which is best temp. linking method of records which are to be sent to a server for insertion in DB

Question

Hi,

[SCENARIO] The scenario is, there is a requirement to send multiple records through XML file to the server, for insertion in the database. These records consists of multiple master and detail tables data linked together through primary and foreign keys.

Now the client cannot fill the Primary key and foreign key columns/data in those records before hand, this must be done by the server when XML data arrives there.

{Client} ---------------> {Our Server} --> {SQL Server}

[QUESTION] What is the best way to temporarily link master and detail records together so that server must understand linked records and substitute temporary primary/foreign keys with GUID's or any auto-number/unique-key as per database schema. ?

Should I use simple sequential integer keys or GUID ? Are there any industry standards ?

Answer 1

I have never heard of any standard way to do this, industry or otherwise. Whenver possible, you want to determine/generate the primary keys before adding rows to the database. Natural keys are ideal for this, and guids would work as well. A brief example:

--  Start with Parent row and several Children rows to insert
SET @GuidPK = newid()
INSERT parent row using @GuidPK
INSERT children rows using @GuidPK

If you cannot do this (which will happen if you're using an identity column as the parent's primary key), and are inserting a single parent (+ 0 or more children), it's still simple:

--  Start with Parent row and several Children rows to insert
INSERT Parent  --  Presums one at a time!
SET @NewPK = scope_identity()
INSERT Children using @NewPK

However, if you are inserting multiple parents and their children all at once (and it sounds like this is what you're facing), it gets tricky. I have had reasonable success with variants of the following methodology.

First add every "new" parent to the parent table. Once this is done, query the parent table and extract the new Id assigned to each parent, and assign it to the appropriate child rows when they're loaded. Psuedo code:

INSERT ParentSet
SELECT NewIds of ParentSet just loaded
INSERT ChildSet using these NewIds

The trick is in identifying and extracting (only) the new parents we just entered. If you have a natrual key (unique product name, OrderId, maybe something based on datetime data was entered), use that. If not, you'll need to fake one. I've done tricks where I initially generated a guid for each parent to be added, set an arbitrary column to that parent during the intial insert, pulled the new ids by reading only for those guids, and then replacing the guids with the proper column value. More psuedo code:

Add a colum to the parent set, configure with a unique guid in each INSERT parent ... column XYZ = new guid SELECT NewId from Parent where XYZ in (list of guids generated) UPDATE parent set XYZ = proper value where (filter or join based on NewId) INSERT children (using the retrieved NewId)

I hope this helps, it's hard to explain without specific structures and sample data.