Azure SQL Database
An Azure relational database service.
Yes, Truncate is not allowed within a transaction in synapse
Cannot Truncate inside a transaction
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
In noticed that deleting inside a transaction works but not truncating does not work when running on Azure Synapse SQL - is there a way around this? seems counterintuitive that we can't roll back a truncate
Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
1 answer
Sort by: Most helpful
-
Nandan Hegde 36,881 Reputation points MVP Volunteer Moderator2022-09-12T13:25:13.547+00:00 -
Nandan Hegde 36,881 Reputation points MVP Volunteer Moderator
2022-09-12T13:25:58.05+00:00 And there is no way. You need to use Delete statement (it would be slow though)
-
Anonymous
2022-09-12T14:13:16.19+00:00 Are CTAs also not allowed?
-
Nandan Hegde 36,881 Reputation points MVP Volunteer Moderator
2022-09-12T15:26:14.887+00:00 Unfortunately CTAS is not supported within transaction. Based on what I remember is DDL is not supported within a transaction.
Hence we initially create a CTAS outside a transaction that would have all data necessary
And than within transaction delete the table and populace the CTAS data.
Sign in to comment -