Isolating MSSQL bcp loads into temporary table

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.sc开发者_如何学Pythonhema1.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.

---

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).

---

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