ADO, ADO.NET v. DTS

What is it about a DTS that allows you to move a table from one server (not
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
MarkHi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:

> What is it about a DTS that allows you to move a table from one server (no
t
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the sam
e
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>