Isolating MSSQL bcp loads into temporary table

Question

I want to empty a temporary table, load it via bcp, and then modify its rows before moving to a production table. The commands would look something like this:

1. truncate table temp1
2. bcp db.schema1.temp1 in import1.txt -h "TABLOCK"
3. exec sp_modify_temp1_rows_move_to_production_table

My question is how can I execute all this as one transaction, not necessarily in the interest of rolling it back, but in order to provide isolation.

Here is the imagined scenario. User1 goes to load import1.txt (the implementation details of how this done, steps 1-3, are hidden to the user). Before step 2 finishes for User1, User2 initiates their own import. Locking mechanisms prevent their import from starting right away. The primary concern is that User2 step 1 will begin as soon as User2's step 2 is done thus clearing the table before User1's step 3 and the process can be completed.

Three additional notes:

1. Client side execution here via ADO (not to be mistaken with ADO.NET)
2. BULK INSERT is NOT an option
3. Prefer doing this via extending some lock mechanism via ADO that can include step 2 in with steps 1 and 2 of the process - if possible - knowing that it is impossible helps here also

Edit: marc_s - please don't change my post with a personal opinion of how to name the product. Do a search on Wikipedia or Microsoft's Bing or here at Stack Overflow for MSSQL. I already tagged the post as sql-server. MSSQL is another search parameter a person may want to use. Its not my fault that this is a common acronym. If you have some link to provide as to why its particularly harmful to use this alias then please provide. Otherwise it looks like you are going out of your way to force your views on others. Thanks.

Answer 1

1) first solution

• add SessionId field to temp1 table
• add load_session_state table PK sessionId, State tinyint (Process step 1, process step 2... Done...)
• sp_modify_temp1_rows_move_to_production_table will execute ONLY records with some sessionId and State = Done. (sp_modify_temp1_rows_move_to_production_table can be executed asynchronously by using a sql agent job)
• truncate should be replaced with DELETE WHERE SessionId = (it is slow, but it has to be done). (it can be executed by the same sql agent job but after moving records to production)
• TABLOCK should be removed from bcp command

2) second solution

• CREATE TABLE temp_XXX
• bcp db.schema1.temp_XXX in import1.txt -h "TABLOCK"
• exec sp_modify_tempXXX_rows_move_to_production_table @table_name = 'temp_XXX' (use dynamic sql with the @table_name parameter)
• DROP TABLE temp_XXX

ADDED

3) use the table load_session_state as a progress view and for lock your process. if there are any records in it, so notify user for waiting. use your previous solution without changes. just use table load_session_state as logical lock

Answer 2

The typical solution is to use applocks acquired via

sp_getapplock 'importing-ABC', 'Exclusive', 'Session';

This way you locks a logical resource (an arbitrary string/name) and, by convention, other users attempting to do the same must attempt to obtain the same applock. While the applock is held, the bcp command can run w/o interference. Note that this does not prevent another user/application that does not respect the protocol (because of ignorance, malevolence or stupidity).