Export a MSSQL compatible script from h2?

[Christoph Läubrich]

I have a use-case where I collect some data in a local h2 database and
later want to dump and import it e.g. in MSSQL.

Is there an option for h2 SCRIPT TO command that takes care of writing a
SQL Script that could be used in e.g. MSSQL?

Or is there some way to transfer the data in a vendor independent way
from H2 to another JDBC compatible database connection?

[Andreas Reichel]

Christoph,

if I was in your shoes I would not use the SCRIPT TO, but instead:

1) generate the SQL Script by myself via JDBC Meta Data

2) optionally parse that SQL Script with JSQLParser and De-Parse it into a MSSQL specific dialect.

In practise, I have done it differently though: the JEDIT editor supports Regex Search and BeanShell expression replacement.

I have had to apply only a few search patterns to turn a H2/Oracle script into MSSQL compliant SQL:

Normal Replace:

NUMBER(                   DECIMAL(

TIMESTAMP                 DATETIME2

SYSDATE                   current_timestamp

CLOB                      VARCHAR(max)

BLOB                      VARBINARY(max)

Jedit Regex + BeanShell:

TABLE (\w*)\.\"?(\w*)\"?   "TABLE [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]"

index (\w*)\.(\w*)         "INDEX " + _2.toLowerCase()

ON (\w*)\.\"?(\w*)\"?      "ON [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]"

(\w*)\.(\w*).NEXTVAL       "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase()

/\*TRANSACT: ([\w\s]*)\*/  _1.toUpperCase()

More rules/search expressions may be needed, depending on your database.

Good luck and best regards

Andreas