If a single SQL file contains 2 SQL statements below each other (as below) for millions of records
Query 1 : select Id into MyTempTable from TableB b join abc a on a.Id = t.Id
Query 2 : Update TableB set Completed =1
Is there any chance that query 2 will execute first for some of the records or do we have a guarantee of sequential execution of those statements?
In another words, will Query 2 execution be started only after Query1 finishes?
If there are multiple queries in a batch then these queries will be executed in the order they appear in the batch.
After the execution of Query 1 is finished then the update query will be executed. You can run debugger to see the sequential execution.
As answered previously by Max Vernon here
Batch statements are only ever executed serially in the order they appear in the batch.
The only difference with your question is that you seem to not want the update to ever complete if the first statement did not run yet or if it failed. If you want both to succeed & fail toghether you should wrap them in an explicit transaction.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
select b.Id
into #MyTempTable
from TableB b
join abc a
on a.Id = b.Id
UPDATE TableB set Completed =1;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH
