Transaction can't be executed

Question

I'm running a statement in Synapse and want to create a new table from a old table, rename both tables, and drop the old table in a transaction block, but it shows me the "111212;Operation cannot be performed within a transaction" error. Could anyone tell me if there is a syntax error in my statement and how can I fix it?

The following is my statement:

BEGIN TRANSACTION;
CREATE TABLE [dbo].[TableA]
WITH
(
DISTRIBUTION = HASH ( [content_id] )
)
AS
SELECT *
FROM [dbo].[TableB]
OPTION (LABEL = 'CTAS : Delete');

RENAME OBJECT [dbo].[TableB] TO [TableB_old]
RENAME OBJECT [dbo].[TableA] TO [TableB]

drop table dbo.TableB_old;
COMMIT ;

Answer 1

Hello @Yumeng Zhang ,
Thanks for the ask and using Microsoft Q&A platform .
Transaction is are only for "Data modification " ( Update , Insert & Delete ) , what you are trying is DDL ( Data definatiion query ) .

Please read more here .https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-ver15

I am pasting the important lines from the article below ,

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Please do let me know how it goes .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

The error message says "Operation cannot be performed in a transaction", which should be clear enough. Remove BEGIN and COMMIT TRANSACTION.