Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data.
I am focusing on a table 'Accounts' that is accessed constantly and contains over 7 million rows. The copy from the 'AccountsAux' (loaded from the bulk file) to 'Accounts' take about 10 minutes, and during that time any selection on this table gets locked.
The insert method looks like this:
INSERT INTO Accounts SELECT * -- actually the 60 columns are here, included the primary key (not identity) FROM AccountsAux
I've tried using WITH (NOLOCK) on the selections, but for what I've read the insertion escalates to a TABLOCK. I don't care getting incomplete results
What options do I have to avoid locking the selections during the insertion?