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+
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.