Migrating bulk data from one database to another having table schema

  • Thread starter Thread starter AmitDB
  • Start date Start date
A

AmitDB

We have a table "Table1" in a database "DB1". There is one more table
"Table2" in different Dataabse "DB2". Table1 and Table2 have same
schema(Structure). From Table1 I want to transfer all the records
(Which may be 3-5 millions) to Table2.
My code goes like -
<For DB1>:

OledbAdapter DA1
DataSet DS1
DA1.Fill(DS1, "Table1") // With query "select * from table1"

<For DB2>:

OledbAdapter DA2
DataSet DS2

DA2.Fill(DS2, DS1.Table[0].TableName); // With query "select * from
table2 where 1=2"
DS2.ReadXml(new StringReader(DS1.GetXml()), XmlReadMode.IgnoreSchema);

DA2.Update(DS1, DS1.Table[0].TableName);

The code is working fine but when data is huge say 4-5 million, then it
is taking too muchh time to migrate. I think explicit conversion of XML
and then reading the same to Dastination DataSet may be time consuming.

There must be some other alternative which can speed up the process to
migrate the bulk data.
I am using DotNet1.2, which does not have bulk copy option
available......
 
We have already completed with the developement of the product and now
facing this performance issue. I tried with the code only once we fill
the dataset and from Source database and calling
DestinationAdapter.Update(SrcDataSet, "Table2") method
But this case is only applicable when the name of the table known at
compile time but if application gets the name of the table at run time
then we need to pass teh table mapping also...can you help me to create
Table mapping object
 
I concur. No matter how much you tune and refine your ADO application, you
won't come close to the performance of BCP or DTS (by orders of magnitude)..
MS thinks so too--that's why BCP is built into ADO.NET 2.0. BCP is not that
tough to code and does not need to be a program at all--you can run it from
a command-line batch.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sahil Malik said:
ADO.NET 1.1 is not the right tool for migrating huge amounts of Data.

Export a DTS package as VB6 code, convert it to VB.NET, you'll have to use
interop.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/




We have a table "Table1" in a database "DB1". There is one more table
"Table2" in different Dataabse "DB2". Table1 and Table2 have same
schema(Structure). From Table1 I want to transfer all the records
(Which may be 3-5 millions) to Table2.
My code goes like -
<For DB1>:

OledbAdapter DA1
DataSet DS1
DA1.Fill(DS1, "Table1") // With query "select * from table1"

<For DB2>:

OledbAdapter DA2
DataSet DS2

DA2.Fill(DS2, DS1.Table[0].TableName); // With query "select * from
table2 where 1=2"
DS2.ReadXml(new StringReader(DS1.GetXml()), XmlReadMode.IgnoreSchema);

DA2.Update(DS1, DS1.Table[0].TableName);

The code is working fine but when data is huge say 4-5 million, then it
is taking too muchh time to migrate. I think explicit conversion of XML
and then reading the same to Dastination DataSet may be time consuming.

There must be some other alternative which can speed up the process to
migrate the bulk data.
I am using DotNet1.2, which does not have bulk copy option
available......
Click to expand...
Click to expand...
 
Hi there,

Is your database SQL-Server or other? If it's SQL-server,
are DB1 and DB2 in the same server?

Elton Wang
(e-mail address removed)
 
We have a table "Table1" in a database "DB1". There is one more table
"Table2" in different Dataabse "DB2". Table1 and Table2 have same
schema(Structure). From Table1 I want to transfer all the records
(Which may be 3-5 millions) to Table2.
My code goes like -
<For DB1>:

OledbAdapter DA1
DataSet DS1
DA1.Fill(DS1, "Table1") // With query "select * from table1"

<For DB2>:

OledbAdapter DA2
DataSet DS2

DA2.Fill(DS2, DS1.Table[0].TableName); // With query "select * from
table2 where 1=2"
DS2.ReadXml(new StringReader(DS1.GetXml()), XmlReadMode.IgnoreSchema);

DA2.Update(DS1, DS1.Table[0].TableName);

The code is working fine but when data is huge say 4-5 million, then it
is taking too muchh time to migrate. I think explicit conversion of XML
and then reading the same to Dastination DataSet may be time consuming.

There must be some other alternative which can speed up the process to
migrate the bulk data.
I am using DotNet1.2, which does not have bulk copy option
available......
Click to expand...

Is this a one time thing, or is the application designed to do this data
transfer?

If this is going to be done again and again you will have to program DTS to do
it, but if it's a one time shot, just use DTS one time. It's easy to do...


Otis Mukinfus
http://www.otismukinfus.com
 
Back
Top