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

[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()

=============================================================

 

[Sahil Malik [MVP]]

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/

 

[W.G. Ryan eMVP]

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.

 

[Sahil Malik [MVP]]

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
----------------------------------------------------------------------------
---------------

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

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()

=============================================================

 

[W.G. Ryan eMVP]

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

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
----------------------------------------------------------------------------
---------------

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

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()

=============================================================

 

[Mark Rae]

All of which was yucky to say the least.

Ah yes, but the trick is to "smile sweetly, and raise the invoice..."

 

[Sahil Malik [MVP]]

LOL

 

[William \(Bill\) Vaughn]

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.
__________________________________

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/

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()

=============================================================

 

[Cor Ligthert]

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.

To give you one,

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

Cor

 

[Sahil Malik [MVP]]

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

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

 

[Miha Markic [MVP C#]]

That reason makes no sense. Why do I *have* to use a dataset for offline
storage?

Why not?

--
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?

 

[Cor Ligthert]

Sahil,

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

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

 

[Paul Clement]

¤ 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)

 

[Paul Clement]

¤ 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)

 

[Sahil Malik [MVP]]

But given the downsides of a dataset for this particular case, would you
still think a dataset is a better choice? Sorry I disagree.

 

[Sahil Malik [MVP]]

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
----------------------------------------------------------------------------
---------------

That reason makes no sense. Why do I *have* to use a dataset for offline
storage?

Why not?

--
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/

 

[Sahil Malik [MVP]]

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
----------------------------------------------------------------------------
---------------

¤ 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

 

[Sahil Malik [MVP]]

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
----------------------------------------------------------------------------
---------------

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.
__________________________________

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/

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()

=============================================================

 

[Paul Clement]

¤ 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)

 

[Cor Ligthert]

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