Clever Geek Handbook

Copying 3B rows to SQL table?

I need to copy a table that has about 3B rows in the same database with NO TEXT / NTEXT or other LOB columns. What are the fastest ways to minimize downtime? I tried bcp, import / export and SELECT INTO. I have been collecting time statistics for each and this requires at least 10 hours of downtime. We are on the fastest SAN (full SC800 with powerful write / read solid state drives).

+3


source to share


1 answer


Create a new table, bulk load using batch transactions like 100,000 rows per load, then add indexes if needed, drag / rename the existing table and rename the new table to production. This is a process I developed and followed for such large SQL tables.



+1


source






More articles:


All Articles