There are some tool (free preferentially) to to migrate data between different DBMS (like from MySql to Firebird, or Sql Server to Sqlite, etc)?
Edit: I want specifically to migrate from "Firebird Embed" to "Sqlite". But in another situations (like migration of my customers websites) I need migrate from MySql to Sql Server (and vice-versa). So, I find tool to migrate between different databases will be really very useful!
The DBMS you're switching to might offer some sort of migration tool, I know that DB2 and MySQL both have tools that come with their DBMS for this task.
What will you be switching to and what were you using before?
SQL Server has SSIS which can be used to migrate data back and forth between lots fo differnent databases. It is not simple and easy to learn intially though. But then good data migration is a complex problem that probably shouldn't be made too easy or mistakes will be made and data will be messed up.
If you have the schema already defined, the most "portable" way is to generate some files that will do the insertions. For a table like
TABLE( FIELD1, FIELD2, FIELD3)
you can have the select like:
SELECT 'INSERT INTO TABLE( FIELD1, FIELD2, FIELD3) VALUES( ''' + FIELD1 + ''', ''' FIELD2 + ''' FIELD3 '''); ' from TABLE
So the principle is to generate the insert statements using the existing data.
you can use some specific tools like dbcomparer
http://www.clevercomponents.com/products/dbcomparer/dbcomparer.asp
but there is many others...
here is an excellent article to guide you through some very simple & easy steps for data migration between mySQL & MSSQL - codeproject
if you do not mind spending some money then the following 2 tools should also serve your purpose.
For migration from one DB to another (most part of the time DB2->Oracle), we usually use an (expensive) ETL. I would then go for a free ETL for your case. There are several ones on the market. Just check if they support your DBMS (or if they can support standard SQL-92 DB by doing simple SELECTs).
The main advantage in an ETL is the 'T' part if you need it and the way that you write your scripts/scenarios once and it takes care of the specificities of each DBMS when running them.
With a quick search, I've seen ETLSE, Scriptella and you have some other links at the bottom of the ETL Wikipedia page.
Hope that helps
EMS is THE place to go for any database work like this. They have trial versions you can use.
www.sqlmanager.net
Have a look at the perl package SQL:Translator in CPAN.
This and its supporting packages handle most common database schemas.
The SQL:Translator:Parser:.... modules parse existing DDL. The SQL:Translator:Producer:... produce the new DDL.
This package is well documented and I have seen it used for a number of critical software migrations.
The tool only handles "schema" SQL (CREATE TABLE etc.) but not queries.
The "dumper" module creates scripts to transfer data from the old to new schemas.
You can check out the Cross-Database Studio. Its not free but it will do the job you want to be done. I've used it to perform DB comparisons but it can by used for migration as well.
MySQL has a free Migration Toolkit GUI that can be used to pull data out of MS SQL Server (and other ODBC?) databases into MySQL. I had a fair bit of success with it a while back.
Many DBMS-agnostic ORM systems can be hacked into doing this if they don't already support it natively. For example, you can set up ActiveRecord to define your schema with migrations, dump your data to fixtures, migrate on the new database, and then load those fixtures. Conveniently in your case, there are ActiveRecord adapters for both FireBird and SQLite.
I would like to suggest TalenD: TalenD
They write new connectors all the time as well.
ESF Database Convert works for the following DB types: Oracle, MySQL, SQL Server, PostgreSQL, IBM DB2, Visual Foxpro, SQLite, FireBird, InterBase, Microsoft Access, Microsoft Excel, Paradox, Lotus, dBase, CSV/Text and transfer any ODBC DSN data source to them. Single user license is about $200
Any programming language with bindings for both DBMS's can do this. It will work for any DBMS to any DBMS, it's free, and more flexible than any application you could find..
Psuedo code:
import firebird
import sqlite
fb = firebird.connect("localhost")
sql = sqlite.connect("./thedb")
for fb_row in fb.query("SELECT id, title, body FROM questions"):
sql.query("INSERT INTO questions (id, title, body) VALUES (%%, %%, %%)",
row.id, row.title, row.body
)
If you don't have bindings for both in the same language, you could easily read the data into a YAML/XML/etc file. Then read the file and write the data to the other database system..
Being a programming language, you can easily deal with any inconsistencies between the two systems (say, one doesn't have a specific field-type), do any processing on the data, and so on..
The dirt-simplest way if the databases support ADO or ODBC is to attach to them simultaneously with Microsoft Access and just treat them as a single consolidated database server.
A lot of people scorn Access, however, and you will need to have a strong personal sense of identity if you ever admit doing it this way.
I guess he found what to do, but I'll give more options for the people that find this question relevant:
You may want to migrate the delta or entire chunks of data regardless of their state in the destination database.
Columbo is a DB tool that can help you if you want to compare results of two different tables or queries, and migrate from one to the other.
Marie Alix can help if you just want to get an entire table or a query, and export it to different format - for example, generate a script of standard INSERT statements to be executed on the destination database.
Both are currently given freely, just for your feedback. We'll be happy to help you to solve your problem.
Nob Hill Software Customer Support