transeferring data between databases

[RZavulunov]

I'm trying to write a page in asp.net where i'll be able to do a mass
transfer of records from one table in the (Oracle) DB to another table
with a matching structure in a different db also in Oracle. Is there
any way i'd be able to do this by loading the records into a Dataset
and doing a mass update into the destination table? I'm hoping to avoid
looping thru the dataset and doing a update each time.


Thanks,

Rafael Zavulunov

 

[Guest]

I have done it using Oracle and SQL server...sounds kinda wierd but here goes.

1) Ran a query to pull Oracle data to a Dataset. In order to get a
DataAdapter to perform the insert into another table the "data changed"(not
the correct attribute name but...) attribute has to be set so the DataAdapter
knows to insert to the database.

2) To get the "data changed" attribute set I made a new table in the dataset
by cloning the one for the query resultset. I then loaded the data from the
first table into the new one. However, I found that the only thing that
seemed to work properly was using the "LoadDataRow()" and traverse every row
in the first table.
Example code:

Dim dt As DataTable = ds.Tables("ResultSet").Clone()
dt.TableName = "Table2"
ds.Tables.Add(dt)
Dim dr As DataRow
For Each dr In ds.Tables("ResultSet").Rows
ds.Tables("Table2").LoadDataRow(dr.ItemArray(), False)
Next

3) Then create your InsertCommand on a DataAdapter to point to the second
table in the database.
4) Update using the DataAdapter and it will to the inserts.

Hope this answers the question...