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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8d0c3bd3ae9f07d6b8f6cc6db3f55c2779d99602.camel%40manticore-projects.com.
