I have 2 tables (on Sql Server 2014)
DestinationTable has a few million rows in it.
Staging table has a few hundred thousand at a time.
I want to mass insert all rows from Staging table into Destination table and then clear out the staging table.
I'm using the INSERT INTO SELECT syntax. The problem is that during the insert (takes a few minutes of time), nobody else can run a query against the DestinationTable due to the Exclusive locking happening.
Am I doing something wrong here?
I've also tried the MERGE WHEN NOT MATCHED THEN INSERT syntax but it has the same issues.
Is there a way I can set up the bulk insert to not lock up the DestinationTable for SELECT statements to work during the process?