Truncate vs Drop & Recreate Table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Truncate vs Drop & Recreate Table

Hello, I am moving data from production to report server nightly. I am just wondering what is the best way to do it. Option 1:
– Truncate Table
– Drop Indexes
– Insert data
– Recreate Indexes Option 2:
– Drop Table
– Select Into
– Create Indexes Or maybe there are any other better options? Thanks!
Option 1 is ideal option as far as I’m concerned.
If its a continued process then use a DTS job to accomplish the task. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Instead of using DTS, I created SP and use Linked Server i.e. ServerName.DBName.dbo.TableName. Is DTS better or Linked Server better? Thanks!
On performance basis DTS is better than Linked server approach.
Why not schedule a own job with a SP. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, I schedule the SP to run nigntly. By the way, why is Option 1 better?
Its faster (I believe) compare to option 2 where you drop the table and do select into… _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>