开发者

DTS Transformation vs Select/Insert into t

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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜