Hi vicenrico I agree with Rami that jdbc and sql is probably the best way to go. Remember: sometimes the long way round isn't so long.
Some suggestions: Forget about using linked tables. They were way too slow when I tested it. Use prepared statements for the read and insert. Read the data in chunks. Select the min/max values for the primary key/unique field of a table, then have a loop where you set those values in the select statement to get the resultset, incrementing by chunk size every loop. Play around to get the best chunk size. Possibly even set it differently for large tables. You can use DatabaseMetaData to make all this generic. Get the source connection metadata, get all tables, for each table get the columns and build your prepared statements dynamically. You can do the same for the insert statements. Then use getObject and setObject of the prepared statement to get the values without caring what data type they are. This also means you won't have to change your code if you add/remove columns. When setting values for the insert, you can check for exceptions and handle those tables/columns differently. eg. if table name is 'movies' and column name is 'release_date' create a Date object from the string, else use getObject. Remember to close your resultsets and clear any temporary collections if you want to keep the memory consumption down. Increase the cache size of the db before you start, and reset it back to normal when you're done. I used a list of table names to extract in a particular order so I didn't have to worry about relationship issues. You can of course disable constraint checking, do the import, and then add the constraint checking back at the end. I did something similar before, and a 2.5 gig database with thousands of items in a table linked to some tables with millions of records and about 100 000 images takes about 10 minutes. Without the images it took about 2.5 minutes. If you only want to select a subset of the data (like only movies available for a particular store, if each store wanted a separate db) the selects would probably make it a bit slower if they're quite complex. Hope it helps On Tuesday, June 12, 2012 11:17:57 PM UTC+2, vicenrico wrote: > > Well. In fact , I would like to know if I could do the next things: > > > 1)Open old database > 2)Select tables/rows of this database and create linked tables. > 3)open new database > 4)Pass the data from linked tables to new tables with "create table as > select"... > > However, I have the new tables created, so I ask if "create table IF > EXISTS as select" would be a right sentence... and this sentence gets the > data from linked tables... > > -- 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/-/ceqxY3XGvNgJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
