Export a MSSQL compatible script from h2?

29 views
Skip to first unread message

Christoph Läubrich

unread,
Jun 29, 2022, 1:21:20 PMJun 29
to H2 Database
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

unread,
Jun 29, 2022, 8:19:59 PMJun 29
to h2-da...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages