Migrating from MS-SQL to H2

[Igal]

hi,

I have a MS-SQL database with dozens of Tables, Stored Procedures, and User Defined Functions.

is there a "simple" way to convert it to H2?

thanks

[Brian]

The tables come over fairly easily using linked tables or schemas.
The stored procedures and UDFs won't come over, they'd need to be
redone for H2.

[fernando paiva]

Hello, I did it with razorsql...but I had to change some tables....Use RazorSQL, export your mysql DDL with Inserts, edit your file that exported and after import to H2 db.

here one example to h2 db.

[code]

CREATE TABLE mytable (

id INT IDENTITY,

name char(10),

primary key(id)

) ;

[/code]

bye and good luck.

2011/11/8 Igal <d...@21solutions.net>

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/M8KknheXgs0J.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

The stored procedures and UDFs won't come over, they'd need to be
redone for H2.

That's true. It doesn't actually matter that much to what database you migrate to, those most likely need to be re-implemented or at least changed. There is little standardization in those areas (that I'm aware of).

Regards,

Thomas

[Igal]

thanks for your reply.

so for the tables you mean something like:

1) create the tables on H2 database

2) link original tables in MSSQL database

3) INSERT H2db SELECT * FROM MSSQLdb ?

is there a "bulk import" that will read exported data from MSSQL?

[Igal]

RazorSQL looks very interesting.  I'll look further into it.  thank you.

[Igal]

hi Thomas,

I was trying to find some sample code for Stored Procedures or UDFs in H2.  the only information I found was for using Java methods and Aliasing them for use as PROCs.

isn't there a simple way to create a proc in SQL code in H2?

for example, let's say that I have the following sample PROC in MSSQL:

CREATE PROC spGetItemsForType( @Type int ) as

  SELECT *

  FROM   ITEMS

  WHERE  TYPE = @Type;

is there an equivalent H2 Stored Procedure?  can you post the sample code?

thanks :)

[Brian]

You may be able to skip the manual table pre-creation by using CREATE
TABLE AS SELECT.

1.) Launch H2 ensuring the downloadable microsoft SQL Server JDBC
driver jar is in your CLASSPATH
2.) in H2: CREATE LINKED TABLE
LNK_TBLNAME( 'com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://
sqlserverhostname','sqlusername','sqlpassword','SqlDatabaseName.dbo.SqlTableName')
READONLY;
3.) in H2: CREATE TABLE TBLNAME AS SELECT * FROM LNK_TBLNAME;

If you're using a sql named instance append \INSTANCENAME to the URL
in the linked table stmt
If you're using windows authentication to mssql, then there's more
prep to do...
a.) ensure the appropriate auth dll bundled with the msjdbc driver is
acessible by H2
b.) append ;IntegratedSecurity=true to the linked table jdbc URL
c.) use NULL without quotes for the sqlusername and sqlpassword
arguments.

If you want to use a query instead of a table or view as the source,
just wrap it in parens and convert internal single quotes to two
single quotes to escape them.

This is my favorite feature of H2 by far.

Good luck!

[Igal]

hi Brian,

thank you for the detailed explanation.  

it looks like I might be able to write a SQL script that will iterate over all the tables in the database and generate a script to create those tables.  

I wonder if it will take column sizes (e.g. varchar(16)) and identity columns properly.

I'll try it out.

thanks again :)

[Noel Grandin]

google for "database schema export tool" and you'll find lots of free
tools already out there.

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/h2-database/-/R3bijmhdgx0J.