As far as I read, this statement is atomic. Either both will fail or both will succeed:
DELETE FROM TABLE1
OUTPUT DELETED.*
INTO TABLE2
WHERE <condition>
I have these statements executed through a stored procedure. When I execute said stored procedure, or these statements directly in SSMS, it works perfectly.
But when I call the stored procedure through ADO.NET SqlCommand which is part of a transaction, then only the delete happens - the Insert is not performed.
When I call the stored procedure through ADO.NET SqlCommand without a transaction, then both delete and insert happen.
Any clue for this behavior?
