Bulk Copy from datasource A to B

[Cip]

Hi,

does anyone have any information on how i would implement my own "Data
Importer" type application, similar to DTS, BCP?

Users would select a datasource (oracle, sql server, text files, etc.
- whatever flexibility i feel like programming) and the destination
(the destination will always be an embedded SQLite database, users
choose the filename)

currently i am achieving it this way:

loop 10000 times
prepare insert into string, get source values from Oracle , SQL, or
text
execute insert into command in destination datasource
loop

and its obviously slow.

how can i achieve bulk copying?

NOTE: this app is to be run on ANY user's machine, regardless of
whether they have DTS installed or the DTS dlls from the SQL Server
redist DLLs. so i want my app to be completely independent; i dont
want a solution that relies on a call to DTS or BCP since that would
create dependencies.

does this make sense?

 

[Guest]

Hi CIP

What I might suggest is using SQL Server's XML features
First You could get data from the DataSource using DataSet and output to XML text, like Dataset.WriteXml()
Then use a stored procedure like the following to insert data
Create Procedure Test(@XmlText as Text
A
DECLARE @idoc in
EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlTex
INSERT DestinationTable (...)
SELECT
FROM OPENXML (@idoc, '/ROOT/Rows'
EXEC sp_xml_removedocument @ido

Hope it will help

Bin Song, MC

----- Cip wrote: ----

Hi

does anyone have any information on how i would implement my own "Dat
Importer" type application, similar to DTS, BCP

Users would select a datasource (oracle, sql server, text files, etc
- whatever flexibility i feel like programming) and the destinatio
(the destination will always be an embedded SQLite database, user
choose the filename

currently i am achieving it this way

loop 10000 time
prepare insert into string, get source values from Oracle , SQL, o
tex
execute insert into command in destination datasourc
loo

and its obviously slow

how can i achieve bulk copying

NOTE: this app is to be run on ANY user's machine, regardless o
whether they have DTS installed or the DTS dlls from the SQL Serve
redist DLLs. so i want my app to be completely independent; i don
want a solution that relies on a call to DTS or BCP since that woul
create dependencies

does this make sense

 

[Cowboy \(Gregory A. Beamer\)]

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. In other words, loop and INSERT for MySql, but bulk for SQL
Server and Oracle.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Cip]

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.

 

[Cowboy \(Gregory A. Beamer\)]

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