Delete All / Bulk Insert

Question

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

Answer 1

I think your fastest method would be to:

1. Drop all foreign keys and indexes from your table.
2. Truncate your table.
3. Bulk insert your data.
4. Recreate your foreign keys and indexes.

Answer 2

Is the problem that Joe's solution is not fast enough, or that you can not have any activity against the target table while your process runs? If you just need to prevent users from running queries against your target table, you should contain your process within a transaction block. This way, when your TRUNCATE TABLE executes, it will create a table lock that will be held for the duration of the transaction, like so:

begin tran;

truncate table stage_table

bulk insert stage_table
from N'C:\datafile.txt'

commit tran;