Re: Bulk Copy from datasource A to B

From: Cowboy \(Gregory A. Beamer\) (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 08:29:23 -0500

I would code the loop, as a worst case scenario. Then, create database
specific solutions if the user selects one of the databases that allow bulk
loading. If you use a DataSet, you can alter the XML, via XSLT, for input
into SQL Server. The same can be done for Oracle. These types of
optimizations will have to be per database, unless you want everything to
use the slowest method. Start with the loop first, just in case you do not
finish other methods.

I imagine you could break down into DTS and create your own, but you would
likely find MS followed a similar methodology. 

-- 
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
**********************************************************************
Think Outside the Box!
**********************************************************************
"Cip" <dafunk2001@yahoo.com> wrote in message
news:9f0f419b.0404121743.44545b29@posting.google.com...
> "Cowboy \(Gregory A. Beamer\)" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:<O8tT4KJIEHA.4052@TK2MSFTNGP12.phx.gbl>...
> > Depends on what you are pulling from and putting into. With Oracle to
SQL
> > Server, for example, you can pull the data out as XML, run an XSLT
transform
> > to create an UpdateGram or DiffGram and put it into SQL Server with one
> > command. If you need more flexibility in DBs, your method is likely to
be
> > the best unless you want to optimize when you find the server to be SQL
> > Server, et al.
>
> Unfortunately I need the DB flexibility.
>
> I have to make it work from "anything" (text, SQL Server, Oracle,
> Access, etc.)
> to my embedded SQLite server.
>
> The destination will always be the same but I need flexibility to use
> any DB as a source.
>
> > In other words, loop and INSERT for MySql, but bulk for SQL
> > Server and Oracle.
>
> I was hoping there was a universal "BULK INSERT" solution... i did not
> want to have to come up with n different solutions for n different
> datasources.
>
> That being said, I will stick with loop+INSERT for all solutions,
> unless someone has anything better.
>
> Do any of the ADO.NET objects have bulk insert functionality that I
> can use for any scenario/datasource?
>
> I was thinking that if ODBC objects have bulk functionality I could
> use OBDC objects for SQL Server, Oracle, Text, MS Access....
>
> currently I am using specific providers for each different datasource
> (SQL Server Provider, Oracle Provider, JET for Access, etc.)
>
> Thanks for your help.

Relevant Pages

  • Re: Why not Access...?
    ... Next, I DID NOT SAY Oracle was considered a "best choice," only a sufficient ... It is a desktop application and the database in also on the same machine. ... I also want to know why Oracle is considered a best choice as compared to SQL Server. ... > Also, you need to answer the question regarding whether or not each client> will be using an individual database, and individual copy of a central> organization database, or making straight calls to a centralized ...
    (microsoft.public.sqlserver.server)
  • Re: Merge/Replication or Syncing with Oracle
    ... You can't directly access an Access database on the desktop from a WM 5 app, but you can sync between SQL Compact on the WM device and Access using the Access Syncronizer: ... As for the Oracle issue ... ... Where SQL Compact Edition easily does merge/replication to SQL Server using IIS ... ...
    (microsoft.public.sqlserver.ce)
  • Re: Oracle vs SQL Server as a back end for Access?
    ... post -- comparing back end database engines or replacing the front end ... say that Oracle 10g is better than SQL Server 2000 without determining ... whether or not the total cost of ownership of 10g over SQL Server ... developer than Oracle 10g, in spite of its new web interface. ...
    (comp.databases.ms-access)
  • Re: Oracle licence question
    ... And no - it does not freeze anything, the backups DO NOT affect connections ... SQL Server MVP ... freeze the database and send it to the target. ... What I really wanted is something that lists features I get in Oracle ...
    (comp.databases.oracle.server)
  • Re: Generating one table with a terabyte of data
    ... On fast bulk loads you should make sure that the database is not logging. ... If the bulk load is fast enough you can always reload ... > I've locked SQL server to 1/3 physical memory usage. ...
    (microsoft.public.sqlserver.dts)
Loading