0

I have a dataset with multiple tables and relations (imported from an XMLreader). I need to insert the data into a SQL database with the related information (the SQL server tables are identical to the dataset tables).

My issue is the identity columns of the imported xml - are always going to be sequence numbers (just like the XML) 

<SysProg Version-1.0><Jobs> <Job xyz /> <Job xyz /> <Jobs> </SysProg>
  • SQLBulkCopy will not work as other previous xml files will have had those same identities. Inserting into SQL DB will be bad.

How can I walk the tables in the dataset, order them from parent to child, so I can update parent with data (get back ID's, then update child) etc..

I know to check for ChildRelations etc.. ordering and then ensuring it is all properly done , I am not sure how to go about it - or if someone has a method out there to do this simply. I am trying not to hard code against the tables - so if the XML changes I do not need to recode against a new xml data model.

EDIT

The data in the data sets comes from an xml file. I get 8 Tables, Parent and then child tables. Each read of an xml file - the id's of the rows with in will be a sequence form 0 to n. I need to insert the data into a database and maintain the relationship of the data. I will always be performing an Insert - I will never be performing an UPDATE. as these are jobs being read into a database for queueing etc..

My DB Server auto- generates the primary keys.

So my question is - how can I walk these tables and insert the data in an efficient and proper manner and easier is better ?

I read this link how-to-update-dataset-parent-child-tables-with-autogenerated-identity-key and it seems promising .. but I am not quite following it. I am guessing I change my numbers in Id columns to negatives and put this thing in a dataadapter and update the database ?

Ken
  • 2,518
  • 2
  • 27
  • 35
  • You have a table with Primary Index which means you can only have one value with the same Primary. SQL server has two commands : Insert and Update. You can only use Insert when the Primary does not exist, and you can only use Update when the Primary Does exist. Both Insert and Update both return a value indicating the number of row changed. So when the return value is zero you then have to use the other command (Insert or Update) to change the data. You can either try Insert first and when you get zero then use Update. Or try Update first and when you get zero use Insert. – jdweng Aug 29 '18 at 16:26
  • @jdweng - I understand the PrimaryKey issue and it is why I am posting. I have the data from xml file into a dataset. The tables are related and those relations will always be sequential Id's the database has no idea of because they are in the xml. The xml files will repeat these numbers by nature,even though the data is different (different files read at different times). This data will always be inserted NEVER an update. XML files will be different, different jobs [ALWAYS] so never an update. – Ken Aug 29 '18 at 16:46
  • But if you run the same data through the system twice you will need to use Update. What happens in the middle of an upload you loose the connection? – jdweng Aug 29 '18 at 16:57
  • @jdweng I will not have the same data. That is the point - the ids will be the same but the data will not be. – Ken Aug 29 '18 at 17:15

0 Answers0