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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 [MVP]" <contactmethrumyblog@xxxxxxxxxx> wrote in message
news:ef9A4vraFHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <sahridhayan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:8BAB5041-C4CB-40D3-AE67-2759104C88E4@xxxxxxxxxxxxxxxx
>> 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()
>>
>> =============================================================
>
>


.