Sample Header Ad - 728x90

I've TABLOCKed a table. How can I read it?

-2 votes
2 answers
905 views
I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked? Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that makes the circumstances non-normal. In this case, **I don't care at all about ACID**. The only thing that I care about is not cancelling the query that has TABLOCKed the table. I've tried WITH (NOLOCK), but signs suggest that it does not work. Am I asking for the impossible? Just to spell it out, suppose that I'm running
INSERT [dbo].[Table1] WITH (TABLOCK)
SELECT * FROM [A_Lot_Of_Rows]
My goal is to query Table1 while that runs. I'm doing bulk inserts and, while they're running, have decided that I want to check some detail. I don't care what row I get said detail from. Each row in A_Lot_Of_Rows has a date in it. A_Lot_Of_Rows is truncated by my stored procedure and it is repopulated in date-based batches before being truncated again. By reading an arbitrary row from the table, I can tell what batch I'm on.
Asked by J. Mini (1237 rep)
Nov 24, 2023, 12:52 PM
Last activity: Nov 25, 2023, 06:59 AM