0

Can someone please tell me if insert select from CTE is still an atomic transaction?

Here is my example:

WITH SELECTION AS (
     SELECT [Code], [Field1], [Field2] 
     FROM [Products] 
     WHERE [Active] = 1 AND Code NOT IN (SELECT [Code] FROM [SomeOtherTable])
)
INSERT INTO JobQueue ([Field1], [Field2], [Code]) 
   SELECT Field1, Field2, Code 
   FROM SELECTION

Please, do not pay attention on T-SQL logic as it is not an exact example of what I am doing.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Gregor Jovan
  • 47
  • 1
  • 6
  • Hello, I believe this thread would help you with the question fi I have understood you correctly. https://stackoverflow.com/q/1071286/12660350 – Gospodin Tanev Jan 23 '20 at 09:19

1 Answers1

0

Of course. There is no exception of transactional behaviour in one single DML query. A single Query is a query beginning by SELECT, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, or WITH...

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
SQLpro
  • 3,994
  • 1
  • 6
  • 14