1

I am working on data (data from tables only) conversion from MSSQL database to H2 database. Data model is the same except maybe a few minor changes.

My first idea was to export data from tables as INSERT INTO statements (like here), and run these after some adjustments on the H2 database. But I ran into a problem with datetime, MSSQL exported this as a number casted like datetime: 

CAST(0x000092AC00000000 AS DateTime)

And H2 doesn't seem to understand this. Is there a way to make this solution work (force SSMS to export the datetime a different way so H2 can read it)? I am using SSMS 2008 R2. I'm also open to other suggestions to solve this problem.

Community
  • 1
  • 1
kwitee
  • 172
  • 4
  • 13
  • 2
    There are two problem: H2 doesn't support such hex number, you would need to write `CAST(X'000092AC00000000' AS LONG)` to convert the hex value to a long (bigint). The second problem is that H2 doesn't support casting numbers to dates. I wonder what the number means? – Thomas Mueller Nov 12 '12 at 16:17

1 Answers1

0

Have you tried Open DB Copy?
http://sourceforge.net/projects/opendbcopy/

jschiavon
  • 527
  • 5
  • 10
  • Thanks for your answer, I actually managed to make a simple .NET application, which selects the data from the MSSQL, and then create a batch of inserts to H2 database. It's a really ugly solution, but it worked fine for me. – kwitee Nov 14 '12 at 15:01
  • Open DB Copy doesn't support H2 – Danny Lo Jan 26 '16 at 15:23
  • It does. H2 is not included in the list of databases OpenDB Copy ships with. You have to add it yourself. Please read the OpenDB Copy user manual. – jschiavon Jan 27 '16 at 22:18