--- In firebird-support@yahoogroups.com, "lutterot" <lutteroth@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "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:
> >
> > EXECUTE BLOCK AS BEGIN
> > INSERT ...
> > CREATE TABLE ...
> >
> > WHEN SQLCODE <0 DO CONTINUE;
> > 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.

Adam