Cannot Truncate inside a transaction

Question

Cannot Truncate inside a transaction

Richard Gao 6

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

1 answer

Your answer

Answer 1

Nandan Hegde 36,146 MVP Volunteer Moderator

Yes, Truncate is not allowed within a transaction in synapse

https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16

Nandan Hegde 36,146 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)
Richard Gao 6 Reputation points

2022-09-12T14:13:16.19+00:00

Are CTAs also not allowed?
Nandan Hegde 36,146 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.

Share via

Cannot Truncate inside a transaction

1 answer

Your answer