Script for running DDL statements in a transaction
Download Red Gate SQL Compare and see how the scripts are generated there.
This does transactional DDL and can be extended for logging.
TRY/CATCH doesn't span batches which makes is trickier to use without dynamic SQL
Execute Immediate on DDL Statements
What you get is a compilation error. The PL/SQL
will be compiled before it is executed. So during compilation, the table you try to SELECT
will not be available. And hence the error. Only if the view name already exists, this will work. Try your select
too as dynamic.
Declare
Stmt varchar2(2000);
Var number;
Begin
Stmt:='create or replace view emp_dept_v as select * from emp';
Execute immediate stmt;
Stmt:='Select count(*) from emp_dept_v';
Execute immediate stmt into var;
Dbms_output. Put_line(var);
End;
/
And by the way, DDLs
do not need a COMMIT
;
Can we use 'GO' multiple times in SQL Transaction?
You are mixing concepts. GO
is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO
is the tools batch delimiter. sqlcmd.exe
and SSMS both are using, by default, GO
as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).
Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).
Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b
at sqlcmd.exe
start, or use :on error exit
in SSMS).
But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.
The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.
Note that what Dan recommends (dynamic SQL) works because sp_executesql
starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.
Related Topics
Best Database Field Type for a Url
SQL for Applying Conditions to Multiple Rows in a Join
How to Get a SQL Row_Number Equivalent for a Spark Rdd
SQL Server 2008 - Help Writing Simple Insert Trigger
What Is the Equivalent of 'Describe Table' in SQL Server
Get Execution Time of Postgresql Query
Sql: How to Select a Single Id ("Row") That Meets Multiple Criteria from a Single Column
Transform and Pivot in Access 2013 SQL
Stored Procedure That Exports Data into CSV Files Only Exports to One File
Delete Parent If It's Not Referenced by Any Other Child
How to Create a Multi-Tenant Database with Shared Table Structures
How to Add 'On Delete Cascade' in Alter Table Statement
Why Can't You Mix Aggregate Values and Non-Aggregate Values in a Single Select
SQL Select to Get the First N Positive Integers
How to Backup a Remote SQL Server Database to a Local Drive