Subject Re: INSERT / CREATE if non-existent
Author Adam
--- In, "lutterot" <lutteroth@...> wrote:
> --- In, "lutterot" <lutteroth@> wrote:
> >
> > Hi!
> >
> > I am looking for a good way to create a table if a table of the same
> > name does not exist, and inserting a row if a row with the same
> > primary key value does not exist, for many rows and tables. That is, I
> > need to merge data and metadata into an existing DB using an SQL
> > script. I have heard that the upcoming Firebird version will support
> > an INSERT OR UPDATE, but I don't know any details.

The 'merge' feature (SQL 2003) has been discussed in the Architect
list, as were some similar ideas (insert or update / replace). I am
not sure about what was decided, but you can search the list yourself.

> >
> > I have tried something like this:
> >
> > INSERT ...
> >
> > END;
> >
> > ...but for some syntactic reason it could not be executed.
> I have read now that DDL cannot be executed in a stored procedure, so
> the CREATE TABLE in EXECUTE BLOCK seems to be forbidden as well.

Furthermore, if you want things to work properly, never mix DDL in the
same transaction as DML.

> However, I am still wondering if there isn't a simple way to make
> Firebird ignore errors in an SQL script, so that the transaction
> continues.

For many operations, there is a create or alter style option.

'Recreate table' drops the table first if it already exists.
'Create or Alter Procedure'
'Create or Alter Trigger'

Most admin tools allow you to define a source and destination
database, and it will create a migration script.