Exception after dropping table

Question

If I execute a procedure that drops a table and then recreate it using 'SELECT INTO'.

IF that procedure raises an exception after dropping the table, does table dropping take place or not?

Answer 1

Unless you wrap them in a transaction,table will be dropped since each statement will be considered as an implicit transaction..

below are some tests

create table t1
(
id int not null primary key
)

drop table t11
insert into t1
select 1 union all select 1

table t11 will be dropped,even though insert will raise an exception..

one more example..

drop table orderstest
print 'dropped table'
waitfor delay '00:00:05'
select * into orderstest
from Orders

now after 2 seconds,kill session and you can still see orderstest being dropped

I checked with some other statements other than select into ,i don't see a reason why select into will behave differently and this applies even if you wrap statements in a stored proc..

IF you want to rollback all,use a transaction or more better use set xact_Abort on

Answer 2

Yes, the dropped table will be gone. I have had this issue when I script a new primary key. Depending on the table, it saves all the data to a table variable in memory, drops the table, creates a new one with the new pk, then loads the data. If the data violates the new pk, the statement fails and the table variable is dropped leaving me with a new table and no data.

My practice is to create the new table with a slightly different name, load the data, change both table names in a statement, then once all the data is confirmed loaded, drop the original table.