On Wed, 2022-06-29 at 19:21 +0200, 'Christoph Läubrich' via H2 Database
wrote:
> 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?
>
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
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/8d0c3bd3ae9f07d6b8f6cc6db3f55c2779d99602.camel%40manticore-projects.com.
