Hi folks,

this is what happened so far:

I have an old version of an app running and a new version which uses a different data model. All table's ids are identity columns (using sybase 15.0).

To import the old data into the new tables, I wrote an importing tool that creates two sessionFactory objects to connect to both databases.

It reads the old entities, creates entites of the new data model, puts the old data into the new entities (with some needed changes) and saves them to the new database.

This is (almost) working fine as long as I let the new entities get their ids generated automatically by sybase. By 'almost' I mean that I have to finetune the creation order of new entities in such a way that no foreign key constraints are violated. This is quite fiddly but I could invest some more time to get this working.

But unfortunately I need some of the new entities to keep the exact same id as before.

So to be able to set the new entities' ids, i removed the @generatedvalue from the new persistent classes but have the database colums still beeing identity columns. Then i tried to use an interceptor to find the right moment to set identity_insert on on the table that the entity is beeing inserted to. Doesn't work properly yet either (e.g. sybase doesnt't allow identity_insert set to ON on more than one table in one session)

To make this long story short: Before I invest even more time in getting those issues mentioned before fixed, are there other ways to achieve what I'm trying to?

I want to:
- migrate data from old to new database (different model, so stuff like bcp wont't work)
- keep id values of some of the entity classes although both new and old db use generated identity as id
- keep existing relationships between entities


Any thoughts are very welcome.

Thanks,
Klaus

Nobody ever did this before? I can't believe that :-(

You're quite right.. this must have been done may times before.

I am beginning work on a quick tool that does the same thing. Though, my legacy database will just be queried with raw JDBC, and then I'll use a Hibernate session to hydrate my POJOs and persist them.

I think you are using the wrong tool for the type of operation you want to achieve. It's probably better you have a look in direction of etl tool, i.e.:
1. search for ETL on google,
2. Example open source product: http://www.talend.com

Yes and No. I agree to an extent, but it really depends on the project.

I have a relatively simple project - in fact a legacy, unnormalized database with only three tables that I need to do ETL on. Writing plain Java code (or code in a scripting language like Groovy or JRuby) is far preferable in this specific case. I tend to be a little skeptical about tools that push an IDE that goes along with them - as they usually have a pretty steep learning curve, that goes way beyond the needs of the project.

Not being a Sybase expert but would it be possible to define the tables you want the ids preserved on first without the identity constraint on the column and then after conversion use an alter table statement to change the column type to identity?

Thanks for your suggestions.

I have my migration tool running properly, fortunately it wasn't that much more effort. I'll have a look at some ETL tool as suggested by knoll before I have to do similar things in the future.

@malm66: I'm not aware of a possibility to alter a regular table column to be an identity column in sybase. I create a temp tables for my new data model (without identity), insert all data into them and then copy it over to the regular tables (which have identity cols) using "insert into", preceded by "SET IDENTITY_INSERT XXX ON". Here the right order matters, to avoid foreign key constraint violations.

Klaus