1

I am having an XML which has data for multiple tables. Now i want to insert/update into database. If the primary key already exists in the database, then it have to update the record from XML, else insert new record. Note: I have millions of records. So please suggest me the best way to do this.

My xml will looks like:

<Table1>
    <Key>1</Key>
    <Value>Data</value>
</Table1>
<Table2>
    <Key>1</Key>
    <Value>Data</Value>
</Table2>
<Table2>
    <Key>2</Key>
    <Value>Updated data</Value>
<Table2>

Existing Database:

Table1                              Table2
---------------------               -------------------------
Key      Value                      Key            Value
 1        Data                       1              Data
                                     2              Data

Expected Result: After importing the XML

Table1                              Table2
---------------------               -------------------------
Key      Value                      Key            Value
 1        Data                       1              Data
                                     2              Updated data

Note: At Table2, the Value for the Key "2" become "Updated data", and remaining records are same. I have millions of records like this.

How can i achieve this in Oracle database using .NET. Please help. Thanks in advance.

Regards,

Kannan

Kannan Mohan
  • 67
  • 1
  • 11

1 Answers1

1

Use the technique provided in this question (XMLTABLE) in combination with MERGE as described here. This solution will be pure SQL and independent of the .Net.

You'll have to code one statement per table and you'll have to hardcode all column names (which is good IMO).

Community
  • 1
  • 1
HAL 9000
  • 3,877
  • 1
  • 23
  • 29
  • Thanks for you reply. Is there any way to use DataAdapter to update the table in database. (DataAdapter.Update(Dataset)). I've tried this. It is working if i am inserting new records. But not, if i want to update the existing record. Please advice. – Kannan Mohan Jul 11 '14 at 11:44
  • DataAdapter.Update is not "the best way" to do this because AFAIK it can only insert or update one record at a time (slow). You'd have to use OracleCommand.ExecuteNonQuery in order to use a MERGE statement which can insert and update *all* records at once (magnitudes faster) – HAL 9000 Jul 11 '14 at 16:48