Hi I need to refresh data in a table from another table. It has about ~14 million records that need to be inserted.
(was planning to use import data task, there are no dependencies except for a view)
The destination table (identical to source table) has a primary key and clustered index. It also has few non clustered indexes.
Do I drop the indexes and the primary key and then insert data and add them back?
Or do I just directly copy data without dropping indexes. This seems to me to negatively impact performance. But, please let me know.
When bulk inserting a large table, one potential performance hit will be index fragmentation, in both the target table's clustered key and perhaps also the non-clustered indexes. It depends on if the clustered key in the target table is sequentually incremented, like a date/time or identity. If they keys are inserted sequentially, there may be insignificant fragmentation. Run a query to check index fragmentation before and after an insert. If it's getting fragmented heavily, then it might make sense to drop indexes and re-create. It also depends on how often this table gets bulk inserted.
"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."