How to export a smallish Access db to H2?

[justaguy]

Hi,

The Access db has 10 tables but some of which has 20+ fields, creating
table one by one by someone like me who's new to H2 would be very
tedious and time-consuming, I wonder if there's a tool for it.

Thanks.

[Thotheolh]

You can try to get Access to export as SQL script and then use H2 to
import the SQL script. The problem is that Access might use certain
SQL statements that H2 would not recognize.

Another way is to export as a CSV from Access and import into H2 as a
CSV which is universally recognized format.

[rado]

Hello,

You can try with http://opendbcopy.sourceforge.net/ or something
similar.

[JavaGuy]

Hi,

> You can try withhttp://opendbcopy.sourceforge.net/or something
> similar.

This looks like a good idea if you need to script db changes and
include them in your build process. Only problem is that H2 isn't in
the list by default and it seems like it hasn't had any development
for a few years now.

If it's a once off process of converting from one db type to another,
you'll probably get going faster by using SquirreL SQL.
http://www.squirrelsql.org

Download and install it, making sure you install the DbCopy plugin.
Go to your Drivers tab, select H2 and browse for the H2 jar file.
Create separate connections to your source and destination dbs.
They're displayed in 2 separate tabs in SquirreL.
Select all the tables from your source connection, right click and
select 'Copy Table'. Now go to your destination db and expand the
tree until you get to 'Table'. Right click on it and select 'Paste
Table'.

It should copy the tables, primary keys, foreign keys, index etc. with
all data to the new db. Sometimes there are issues between databases
(from h2 to postgres with blobs), but It's worked on many occasions.

If there are problems with db copy plugin, you could also select your
tables, right click and select 'Create Table Script' and manually
modify the sql to fix the inconsistencies between dbs. There is also
an option to create a data script. Just ensure you install the
refactoring and all scripting plugins.

If Squirrel doesn't work for some reason you could look at DdlUtils
(http://db.apache.org/ddlutils), but that doesn't have a nice GUI,
just xml files. I use it to generate a db structure from a source db,
but programmatically copy only certain related data with
transformations using Java.

Hope it helps.

[zing]

If you're doing this once and you have 10 tables, it would only take
20 easy statements to copy them, assuming that you have an ODBC or
JDBC connection to your database:

create linked table .... (see syntax guide)
create table as select * from < linked table 1 name> ...
etc...

You don't need to ever know or mention any column names.

On Nov 29, 1:25 am, JavaGuy <thirion.franc...@gmail.com> wrote:
> Hi,
>
> > You can try withhttp://opendbcopy.sourceforge.net/orsomething
> > similar.
>
> This looks like a good idea if you need to script db changes and
> include them in your build process.  Only problem is that H2 isn't in
> the list by default and it seems like it hasn't had any development
> for a few years now.
>
> If it's a once off process of converting from one db type to another,

> you'll probably get going faster by using SquirreL SQL.http://www.squirrelsql.org