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

I think you need to do

1. query the data out from MS SQL

2. import the data into in-memory DB with its API

Either SQL expressions or DB related APIs

Answer 2

You could dump the data as SQL INSERT statements then read it back.

You could read to a temporay object (like a struct) then write back to the internal db.

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

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