Would DTS be faster for transferring data or script

[sonname]

I have a table that I need to export from one database to another. My qestion is, would a DTS package be faster than individual insert statements in query analyzer to transfer over the data. The table is a rather large table.

Thanks

 

[Crowley16]

the sql statements will be faster.

Although for dts, I have once used the object automation method when running a dts and found that to be extremely fast, as compaired to running the dts normally from em, make of that what you will...

--------------------
Procrastinate Now!

 

[LNBruno]

individual insert statements

For an entire table?

SELECT * INTO newTable FROM otherTable

< M!ke >
Acupuncture Development: a jab well done.

 

[gmmastros]

>> I have a table that I need to export from one database to another

Are these databases on the same server? If so, a modified version of LBruno's advice is the way to go.

Select * 
Into   TargetDatabase.dbo.NewTable
From   SourceDatabase.dbo.ExistingTable

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

...and, tacking onto George's suggestion, if you don't want ALL the records from the ExistingTable, add a WHERE clause...

< M!ke >
Acupuncture Development: a jab well done.

 

[Crowley16]

even if they are on different servers, or even with different database engines, you can add the server as a linked server, and use direct sql...

--------------------
Procrastinate Now!

 

[SQLDenis]

USE BCP OUT and BCP IN/BULK INSERT if it is a lot of data, drop the indexes before loading the data to speed it up even more

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions