exporting data from one db to another.. using Microsoft Providers.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Can some one please post a sample or site where I can get code for
transfering data from a database (Oracle) and put it in another (Access).(or
from access to odbc oracle..) The data can be SELECTed with filters and
modifications for reporting purposes.
It will be great if the sample could show I could use dataset as a temporary
storage and use it to push data into multiple databases, either into new
tables or append tables in databases.
thanks,
sahridhayan

PS: There was a post with an example something like below.. will this work
perfectly fine for large..inserts, this idea is very close to what I wish to
do. But I am open to any other procedre:
=============================================================

Dim cmdCopy As OleDbCommand

cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
[Provider={MSDAORA.1\};User ID=admin;Data
Source=src;Password=***].[document]", myLocalConnection)

nRows = cmdCopy.ExecuteNonQuery()

=============================================================
 
sahridhayan,

ADO.NET 1.1 is not a good tool to ferry large amounts of data between
databases. By using a dataset/data-adapter based approach you would incur
the overhead of a huge dataset, and have to execute one query per row. Not
to mention hierarchical data will be a royal pain.

You are better off using an ODBC Linked table through Access, and perform
this operation via interop.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
 
I totally agree with Sahil but if you *must* do it w/ ADO.NET, create two
DataAdapters, one for the source, one for the destination. On the source
adapter, set the AcceptChangesDuringFill property to false. Then fill the
datatable. Immediately thereafter, use the second adapter, passing in the
SAME datatable, and call Update on it. Here's an example
http://www.knowdotnet.com/articles/datasetmerge.html

Note thought that I only provide this if you *must* do it this way. If you
have a lot of data, it's going to be Slow and this is straightforward code
wise, but not efficient by any standard.
 
I can't think of a good reason that he *must* do it with ADO.NET :-).
Interop is such a better solution for this problem. But I agree, if he
*must* - but I'd be curious to know his reasons.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------



W.G. Ryan eMVP said:
I totally agree with Sahil but if you *must* do it w/ ADO.NET, create two
DataAdapters, one for the source, one for the destination. On the source
adapter, set the AcceptChangesDuringFill property to false. Then fill the
datatable. Immediately thereafter, use the second adapter, passing in the
SAME datatable, and call Update on it. Here's an example
http://www.knowdotnet.com/articles/datasetmerge.html

Note thought that I only provide this if you *must* do it this way. If you
have a lot of data, it's going to be Slow and this is straightforward code
wise, but not efficient by any standard.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
sahridhayan said:
Hi,
Can some one please post a sample or site where I can get code for
transfering data from a database (Oracle) and put it in another
(Access).(or
from access to odbc oracle..) The data can be SELECTed with filters and
modifications for reporting purposes.
It will be great if the sample could show I could use dataset as a
temporary
storage and use it to push data into multiple databases, either into new
tables or append tables in databases.
thanks,
sahridhayan

PS: There was a post with an example something like below.. will this work
perfectly fine for large..inserts, this idea is very close to what I wish
to
do. But I am open to any other procedre:
=============================================================

Dim cmdCopy As OleDbCommand

cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
[Provider={MSDAORA.1\};User ID=admin;Data
Source=src;Password=***].[document]", myLocalConnection)

nRows = cmdCopy.ExecuteNonQuery()

=============================================================
Click to expand...
Click to expand...
 
There are a lot of ridiculous bosses in the world. THere were many things
that I 'must' do in the past that were absurd but the guy signing the
paychecks wanted it that way so that's how it was done. Amongst the dummer
things:

Must turn off option strict (on code that did NOT need late binding)
Must make a button blink on a Winform
Must store data redundantly in three tables even though it wasn't needed
Must have 250 fields in a table

All of which was yucky to say the least.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Sahil Malik said:
I can't think of a good reason that he *must* do it with ADO.NET :-).
Interop is such a better solution for this problem. But I agree, if he
*must* - but I'd be curious to know his reasons.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------



W.G. Ryan eMVP said:
I totally agree with Sahil but if you *must* do it w/ ADO.NET, create two
DataAdapters, one for the source, one for the destination. On the source
adapter, set the AcceptChangesDuringFill property to false. Then fill
the
datatable. Immediately thereafter, use the second adapter, passing in
the
SAME datatable, and call Update on it. Here's an example
http://www.knowdotnet.com/articles/datasetmerge.html

Note thought that I only provide this if you *must* do it this way. If
you
have a lot of data, it's going to be Slow and this is straightforward
code
wise, but not efficient by any standard.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
sahridhayan said:
Hi,
Can some one please post a sample or site where I can get code for
transfering data from a database (Oracle) and put it in another
(Access).(or
from access to odbc oracle..) The data can be SELECTed with filters and
modifications for reporting purposes.
It will be great if the sample could show I could use dataset as a
temporary
storage and use it to push data into multiple databases, either into
new
tables or append tables in databases.
thanks,
sahridhayan

PS: There was a post with an example something like below.. will this work
perfectly fine for large..inserts, this idea is very close to what I wish
to
do. But I am open to any other procedre:
=============================================================

Dim cmdCopy As OleDbCommand

cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
[Provider={MSDAORA.1\};User ID=admin;Data
Source=src;Password=***].[document]", myLocalConnection)

nRows = cmdCopy.ExecuteNonQuery()

=============================================================
Click to expand...
Click to expand...
Click to expand...
 
Ah no... Again ADO (any version short of 2.0) does not have what it takes to
make bulk data transfers. That's what BCP is for. It can be called directly
on the SQL Server engine using the BCP commands, from SMO or SQL DMO or from
BCP command batches. It outperforms ADO by an order of magnitude (or
better).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
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:
sahridhayan,

ADO.NET 1.1 is not a good tool to ferry large amounts of data between
databases. By using a dataset/data-adapter based approach you would incur
the overhead of a huge dataset, and have to execute one query per row. Not
to mention hierarchical data will be a royal pain.

You are better off using an ODBC Linked table through Access, and perform
this operation via interop.

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





sahridhayan said:
Hi,
Can some one please post a sample or site where I can get code for
transfering data from a database (Oracle) and put it in another (Access).(or
from access to odbc oracle..) The data can be SELECTed with filters and
modifications for reporting purposes.
It will be great if the sample could show I could use dataset as a temporary
storage and use it to push data into multiple databases, either into new
tables or append tables in databases.
thanks,
sahridhayan

PS: There was a post with an example something like below.. will this
work
perfectly fine for large..inserts, this idea is very close to what I wish to
do. But I am open to any other procedre:
=============================================================

Dim cmdCopy As OleDbCommand

cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
[Provider={MSDAORA.1\};User ID=admin;Data
Source=src;Password=***].[document]", myLocalConnection)

nRows = cmdCopy.ExecuteNonQuery()

=============================================================
Click to expand...
Click to expand...
 
Sahil,
I can't think of a good reason that he *must* do it with ADO.NET :-).
Interop is such a better solution for this problem. But I agree, if he
*must* - but I'd be curious to know his reasons.
Click to expand...

To give you one,

The access database can be on a computer with not any network connection.

Cor
 
Sahil,
That reason makes no sense. Why do I *have* to use a dataset for offline
storage? Why not just a delimited ascii file?
Click to expand...

Who said *have* to. As they take you to type it in for free, than it can as
well be a solution. Although I would even than go for the dataset solution.
To be sure that text is not written in another way than it was read.

I wrote.Cor
 
¤ Ah no... Again ADO (any version short of 2.0) does not have what it takes to
¤ make bulk data transfers. That's what BCP is for. It can be called directly
¤ on the SQL Server engine using the BCP commands, from SMO or SQL DMO or from
¤ BCP command batches. It outperforms ADO by an order of magnitude (or
¤ better).

Of course Oracle uses a different mechanism so BCP won't help the poster in this instance.

In most instances, especially when dealing with smaller ISAM databases, ADO/ADO.NET and SQL is
plenty fast enough for handling data imports and exports.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤ Hi,
¤ Can some one please post a sample or site where I can get code for
¤ transfering data from a database (Oracle) and put it in another (Access).(or
¤ from access to odbc oracle..) The data can be SELECTed with filters and
¤ modifications for reporting purposes.
¤ It will be great if the sample could show I could use dataset as a temporary
¤ storage and use it to push data into multiple databases, either into new
¤ tables or append tables in databases.
¤ thanks,
¤ sahridhayan
¤
¤ PS: There was a post with an example something like below.. will this work
¤ perfectly fine for large..inserts, this idea is very close to what I wish to
¤ do. But I am open to any other procedre:
¤ =============================================================
¤
¤ Dim cmdCopy As OleDbCommand
¤
¤ cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
¤ [Provider={MSDAORA.1\};User ID=admin;Data
¤ Source=src;Password=***].[document]", myLocalConnection)
¤
¤ nRows = cmdCopy.ExecuteNonQuery()
¤
¤ =============================================================

You can use the above method, bypassing Access, and that would be adequate. You can also create
linked Oracle tables in the Access database and perform your SQL imports/exports using the native
and linked Access tables.

Another option would be to use Oracle's SQLLoader facility.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
But given the downsides of a dataset for this particular case, would you
still think a dataset is a better choice? Sorry I disagree.
 
Well because datasets will be persisted back into the db row by row - which
is inefficient. That is why I wouldn't.

--

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

Miha Markic said:
Sahil Malik said:
That reason makes no sense. Why do I *have* to use a dataset for offline
storage?
Click to expand...

Why not? :-P

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Why not just a delimited ascii file?
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
Click to expand...
Click to expand...
 
Nice .. I like this method :-). This pretty much acheives what I was saying,
without dropping to interop yourself.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

Paul Clement said:
¤ Hi,
¤ Can some one please post a sample or site where I can get code for
¤ transfering data from a database (Oracle) and put it in another (Access).(or
¤ from access to odbc oracle..) The data can be SELECTed with filters and
¤ modifications for reporting purposes.
¤ It will be great if the sample could show I could use dataset as a temporary
¤ storage and use it to push data into multiple databases, either into new
¤ tables or append tables in databases.
¤ thanks,
¤ sahridhayan
¤
¤ PS: There was a post with an example something like below.. will this work
¤ perfectly fine for large..inserts, this idea is very close to what I wish to
¤ do. But I am open to any other procedre:
¤ =============================================================
¤
¤ Dim cmdCopy As OleDbCommand
¤
¤ cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
¤ [Provider={MSDAORA.1\};User ID=admin;Data
¤ Source=src;Password=***].[document]", myLocalConnection)
¤
¤ nRows = cmdCopy.ExecuteNonQuery()
¤
¤ =============================================================

You can use the above method, bypassing Access, and that would be adequate. You can also create
linked Oracle tables in the Access database and perform your SQL
Click to expand...
imports/exports using the native
Click to expand...
 
Sahil been misunderstood.

Y'know how you can link and import data from access over an ODBC connection?
That can be done programmatically using the COM programming model for
Access - That's what Sahil was recommending, not BCP or anything like that.

Anyway, Paul's answer is even nicer.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

William (Bill) Vaughn said:
Ah no... Again ADO (any version short of 2.0) does not have what it takes to
make bulk data transfers. That's what BCP is for. It can be called directly
on the SQL Server engine using the BCP commands, from SMO or SQL DMO or from
BCP command batches. It outperforms ADO by an order of magnitude (or
better).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
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:
sahridhayan,

ADO.NET 1.1 is not a good tool to ferry large amounts of data between
databases. By using a dataset/data-adapter based approach you would incur
the overhead of a huge dataset, and have to execute one query per row. Not
to mention hierarchical data will be a royal pain.

You are better off using an ODBC Linked table through Access, and perform
this operation via interop.

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





sahridhayan said:
Hi,
Can some one please post a sample or site where I can get code for
transfering data from a database (Oracle) and put it in another (Access).(or
from access to odbc oracle..) The data can be SELECTed with filters and
modifications for reporting purposes.
It will be great if the sample could show I could use dataset as a temporary
storage and use it to push data into multiple databases, either into new
tables or append tables in databases.
thanks,
sahridhayan

PS: There was a post with an example something like below.. will this
work
perfectly fine for large..inserts, this idea is very close to what I
Click to expand...
wish
to
do. But I am open to any other procedre:
=============================================================

Dim cmdCopy As OleDbCommand

cmdCopy = New OleDbCommand("SELECT * INTO [admin_document_new] FROM
[Provider={MSDAORA.1\};User ID=admin;Data
Source=src;Password=***].[document]", myLocalConnection)

nRows = cmdCopy.ExecuteNonQuery()

=============================================================
Click to expand...
Click to expand...
Click to expand...
 
¤ Nice .. I like this method :-). This pretty much acheives what I was saying,
¤ without dropping to interop yourself.
¤
¤ - Sahil Malik [MVP]
¤ Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
¤ ----------------------------------------------------------------------------

Yeah, no exposed interop involved - just the 'under the covers' communication between the native
..NET OLEDB classes and OLEDB provider.

I prefer it over database specific utilities and ADO.NET DataSets which require database specific
knowledge and a bit of extra coding respectively.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Sahil,

How do you come consequently on that dataset, it was your suggestion to use
a delimited textfile instead of a dataset. I had until than not spoken in
this thread about a dataset. However, told as answer on your suggestion to
use a delimited textfile, that I than would prefer a dataset.

I would go first for standard tools which are available for that and not for
your sugestion to use interop. I gave you in this thread on your
question"when would interop not go" an answer in which was no dataset.
However your reply on that had nothing to do with that. It was about a
dataset.

Maybe are you mixing up threads.

:-)

Cor
 
Back
Top