Delete All / Bulk Insert
bulkinsert, performance, sql, sql-server, sql-server-2005
Solution
I think your fastest method would be to:
- Drop all foreign keys and indexes from your table.
- Truncate your table.
- Bulk insert your data.
- Recreate your foreign keys and indexes.
Problem
First off let me say I am running on SQL Server 2005 so I don't have access to `MERGE`. I have a table with ~150k rows that I am updating daily from a text file. As rows fall out of the text file I need to delete them from the database and if they change or are new I need to update/insert accordingly. After some testing I've found that performance wise it is exponentially faster to do a full delete and then bulk insert from the text file rather than read through the file line by line doing an update/insert. However I recently came across some posts discussing mimicking the `MERGE` functionality of SQL Server 2008 using a temp table and the output of the `UPDATE` statement. I was interested in this because I am looking into how I can eliminate the time in my Delete/Bulk Insert method when the table has no rows. I still think that this method will be the fastest so I am looking for the best way to solve the empty table problem. Thanks