Luis Cazares (4/14/2015)
sql-lover (4/14/2015)
Hmmm .... thinking loud and to myself.I still would like to hear any advice but I think I can't rollback DDL statements in TSQL. In other words, and after re-reading the my code, I think that my best bet is use the PRINT statement to revise the last successful swap and resume from there.
You can rollback DDL statements in TSQL the same way you can rollback DML statements.
You can't rollback DDL statements in PL/SQL, because it will autocommit the current transaction.
Point for SQL Server. 😀
Take a look on my code...
If I suddenly stop my script after the data has been transferred to the new table, I may end with partially renamed objects inside the last database where the statements were running, those are my DDL statements.
My question is, can I rollback those as well, so I won't end with a partially renamed objects? If that's true, what would be the TSQL code or changes I will have to made.
I can rely on the last PRINT statement, if I halted the script abruptly and assume it did not complete on that last database then manually rename, etc. But I was looking for a more elegant and automatic way.