How to import data to an in-memory database?

Question

Are there any ways to import data in databases such as MS SQL, MySQL into in-memory databases like HSQLDB, H2 etc ?

Answer 1

You can use dbunit for dumping the database to xml files and importing it back to another rdbms.

Answer 2

H2 supports a special database URL that initialized the database from a SQL script file:

"jdbc:h2:mem;INIT=RUNSCRIPT FROM '~/create.sql'"

HSQLDB and Apache Derby don't support such a feature as far as I know.

Answer 3

Look at the free "universal database converter" http://eva-3-universal-database-converter-udc.optadat-com.qarchive.org/ -- it does claim to support MySQL, MS-SQL, and HSQLDB, among others.

Answer 4

It really depends on what ways you think about. Is there a tool that could do it automatically without programming? Maybe.

Do you want to develop it? Then find out whether your favorite language supports both database engines(standard and in memory) and if it does, just write a script that does it. Process everything in chunks(fetch n rows at a time then insert them; repeat). How big the chunk size? It's up to you, try different sizes(say 100, 500, 1k etc.) see which one performs better on your hardware, fine tune to the sweet spot.

If your favorite language on the other hand doesn't support both of them, try using something that does.

Answer 5

In Hibernate: Adding import.sql to the class path works great, hbm2dll checks if the file exists and executes it. The only details is that every sql command most be on one row, otherwise it will fail to execute

Answer 6

Latest versions of HSQLDB allow you to open a CSV (comma separated values) or other delimiter separated data file as a TEXT TABLE in HSQLDB even with mem: databases, which can then be copied to other tables.

As others have pointed out, there are also capable and well maintained third party tools for this purpose.