Faking Create Or Replace Table

The new CREATE OR REPLACE feature of SQL has been most helpful to me. It works for aliases, functions, masks, permissions, procedures, sequences, triggers, variables and views. It would be nice if it worked for tables, especially when I’m developing a new application. Here’s a workaround.

The trick is to use a dynamic compound statement. If you’re not familiar with these, I recommend you read Michael Sansoterra’s excellent article on that subject.

One of the things that dynamic compound statements let you do is include conditional logic, which is what we need. We need a way to drop a table if it exists, and not error out if the table does not exist.

Here’s a typical script of the sort that I run under RUNSQLSTM:

/* Delete the tables if they exist */
begin
   declare continue handler for sqlexception
      begin end;
   drop table customers;
   drop table shipments;
   drop table invoices;
end;

create table customers 
. . . code omitted . . .

create table shipments
. . . code omitted . . .
                      
create table invoices 
. . . code omitted . . .

The dynamic compound statement is the begin-end block that spans the second through the eighth lines. I’ve defined a continue handler to run an empty begin-end block when an exception occurs. The continue handler takes control when a drop table ends abnormally.

This technique is not exactly like Create or Replace. There is one big difference. When you delete an object, the system discards all object authority for that object. Create or Replace retains object authority.

RELATED STORY

Dynamic Compound Statements In DB2 For i