Prevent table exclusive lock when inserting large number of rows from one table to another (Sql Server 2014)

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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try BULK INSERT.
MikeCausiAuthor Commented:
I thought BULK INSERT was only from a data file (ie. txt file) to database.   Didn't think you could go from table to table via BULK INSERT.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're right but if you export the source table to a file then you can bulk insert otherwise you'll run into locks.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

MikeCausiAuthor Commented:
Thanks but I solved my problem using this idea:

"Read committed snapshot isolation (RCSI) was introduced in SQL Server 2005 as a new mechanism to prevent queries reading data to block, or be blocked by, other queries modifying data in the same tables. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint. While RCSI originally targeted scenarios common to OLTP workloads, the feature can be a powerful tool in data warehouse workloads or scenarios involving large-scale bulk insert operations.
RCSI is enabled as a database-wide setting. When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's an option that I would not recommend unless you can control every transactions in your system because you can deal with panthom records.
"Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows."
MikeCausiAuthor Commented:
In my solution I'm ok with returning only the records that were last imported (only we control the records going into the system).  Does not need to be up to the second accurate.
Scott PletcherSenior DBACommented:
RCSI is the best method in these types of cases.

Also, for best overall performance, try to cluster the table such that the new INSERTs are in a contiguous range of keys rather than spread out over the table.  Most often a related datetime column works best.  Identity is a good failback, but only if a relevant datetime is not available.
MikeCausiAuthor Commented:
Was the easiest method to implement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.