Using sql server 2012.
Have one table insertion from Table A to Table B in a loop
With a flag=0 in TABLE B.
A scheduler is running at 5 minute interval reading data in a batch of 2000 from Table B and processing it.
After process, update Flag =1.
When there was around 20,000 data in Table A, it inserted only around 16,000 records and stopped.
Is it because of simultaneously read and write operations?
What is the ideal way to avoid partial insertion?
Are you saying that you are copying data from TableA into TableB in 2000 row batches? And that you want to ensure that the data is only inserted once into TableA?
If yes, you'll need to provide an example.
When posting questions on a forum, you should provide the Data Definition Language (DDL) of your tables, some sample data, and the expected results. If you're troubleshooting, you should also post the actual results you're getting, so we can compare the differences.
Some more tips:
Let me clarify
≈=========SourceTable=========
MId Refno Amnt Date IsMoved
1 aaaa 500 2023-10-01 05:10 1
:
:
20000 zzzz 800 2023-10-01 05:15 0
==≈=====///DestinationTable=======
Xid Refno Amount StateRef Date
1 aaaa 500 sssx 2023-10-01 05:10
:
16000 hhh 300 sssh 2023-10-01 05:12
Issue : Not full data is getting inserted at times.
Assumption is due to same time read and write happening.
Tried way-
If the current time and last inserted time
ismore than 10 minutes, then do the reading from destination table.
In other words, SourceTable is inserted by an application button click. Destination table is inserted via scheduler from source in 5min interval.
Scheduler take data in batch, process and mark flag=1 at the end.
Scenario is if scheduler run at 4pm . It starts reading with flag=0 records. Same time if the end user did a button click, record will be inserted with flag =0.
So, there may be chance like reading the same record inserting and cause conflict.
How can we handle such situation?
Please advise
You could use UPDATE with OUTPUT on the scheduler - that will lock the table for the update and output the selected rows into a table variable. You then select from the table variable for the data.