How to guarantee all data is inserted into table in same transaction?

Question

I have a insert query that looks like this:

INSERT INTO Data1 (column1, column2)
SELECT column1, column2 FROM Data2

This query takes about 30 secs. Within that 30 secs someone might insert, delete, or modify data in Data2 table while the query is still running. How can I either prevent that or include changes within same transaction?

A single insert statement should take place in a single transaction. — Gordon Linoff, Jul 18 '17 at 02:07
I believe while a table is being modified it has exclusive access to it, all other requests will be queued until that `INSERT` is complete. Maybe you want to do multiple queries, in that case, you answered your own question, use transactions. — Havenard, Jul 18 '17 at 02:16
@Havenard I just tedted this. Unfortunetly, this is not the case. I just tries inserting multiple records but did not commit the transaction. Then I inserted a record in Data2. The data was inserted. — Luke101, Jul 18 '17 at 02:46
Possible duplicate of [Correct way to take a exclusive lock](https://stackoverflow.com/questions/13435997/correct-way-to-take-a-exclusive-lock) — Havenard, Jul 18 '17 at 02:55

score 0 · Answer 1 · answered Jul 18 '17 at 05:01

0

Modify your Query WITH(NOLOCK) USE this

INSERT INTO Data1 (column1, column2)
SELECT column1, column2 FROM Data2 WITH(NOLOCK)

this will work.

answered Jul 18 '17 at 05:01

Santhosh Raja

39
1
9

score 0 · Answer 2 · answered Jul 26 '17 at 03:08

If you need to be absolutely certain to have to data changes are made during the insert, you can use the TABLOCKX query hint...

INSERT INTO Data1 WITH (TABLOCKX) (column1, column2)
SELECT column1, column2 FROM Data2 WITH (TABLOCKX);

This will give you exclusive locks on both tables until the transaction completes.

How to guarantee all data is inserted into table in same transaction?

2 Answers2