DTS Transformation vs Select/Insert into t

Question

Does anybody know what the recommend method is to copy data from one table to another using a DTS? Should we use the standard Insert/Select statement or a DTS transformation between two connections? Which one is faster? Has anybody done every any performance test on this?

I am using SQL Server 2000 right now, but would also like to know how the performance is on SQL2005+

Answer 1

If you ever upgrade to SQL 2005, you'll have to rewrite any DTS packages. They're deprecated in favor of SQL Server Integration Services.

So I'd go for the straight SQL, or perhaps the bcp utility.

Answer 2

I would not use DTS for this task and just use T-SQL

1. Drop the destination table
2. Use SELECT INTO to copy the data from server 1 to server 2
3. Create indexes on the destination table

If the schema of the destination table is not exactly the same as the source, you can manipulate the SELECT statement to get it how you want it.

Since you're on SQL 2000, you can't take advantage of the INSERT with TABLOCK to get the bulk operation without dropping the table.