Handling insert and delete with multiple users in SQL

Question

I would like to maintain a system for uploading data through Excel to SQL Server with ADO method. The process consists of two steps:

1. the raw data is inserted to temporary table, say dbo.TableTemp
2. the raw data is processed with a stored procedure and inserted to a dbo.GoodTable
3. delete from dbo.TableTemp at the end of stored procedure

Is there any way to be sure that the activities of two users not overlap? For example the delete from dbo.TableTemp of user1 will not be executed after user2 inserts data and before the data are processed?

Update. Unluckily I have not been successful with #temp tables. They seem to be too much temporary and when I try to insert data into them #temps already do not exist. For uploading data I use the variation of code by Sergey Vaselenko downloaded from here: http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel Data Export to SQL Server using ADO

In the Sergey's solution it is possible to create table by stored procedure prior to inserting the data in step 1. But when I create #temp table with stored procedure, it vanishes at the end of procedure, so I cannot insert data to it. Any help please?

Answer 1

Use temporary tables #TableTemp. Those are specific for each session and thus would not overlap.

There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

Update. Looks like this particular Excel-SQL Server Import-Export using VBA use separate functions to create table and upload the data each opening and closing own connection. From SQL Server perspective those functions operate in different sessions and thus temporary tables do not persist. I think this solution can be rewritten to use single connection to create temporary table, populate, process the data and output the results into permanent table.

You might also find useful this question: How do I make an ADODB.Connection Persistent in VBA in Excel? In particular - Kevin Pope's answer suggesting the use of global connection variable opened and closed with the workbook itself:

Global dbConnPublic As ADODB.Connection

In the "ThisWorkbook" object:

Private Sub Workbook_Open()
    Set dbConnPublic = openDBConn() 'Or whatever your DB connection function is called
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    dbConnPublic.Close
End Sub