Is there any way to copy table from database to another one?

[mismail]

Hi everybody,

I am trying to copy one table from access database to a sqlite database.
Is there any way to do that?
I appreciate your suggestions.

[lgeyer]

I don't think there is something more convenient than a simple loop when it comes to copy data between different DBMSs. If this is just a one-time transfer and needs not to be done programmatically I would go for an CVS or SQL export and import.

[fluca1978]

My suggestion is to avoid it at all in your application, unless you have a very good reason for that. Databases have very good tools for exporting and importing data, that are usually much more efficient than looping across data. Then it depends on which database you are starting from. Many enterprise database (Oracle, PostgreSQL) provides foreign tables, that are the implementation of the sql med, and offer you a way to access tables in other database so that migrating data becomes no more than a SELECT INTO.
If you are really constrained to do the migration within your application than you have to open two different connections, read from the source, insert in the destination and loop until source has no more data.

Ops...sorry I read too late you were talking about MS Access as source database....then I suspect you will have to loop or use a CVS intermediate format,

[mismail]

Well, it seems that there is no way to copy the data except the loop way.
BTW, Is there any way to know the types of the fields in the source database?
Thank you very much

[fluca1978]

Well you can inspect fields using "QSqlField":http://doc.qt.nokia.com/latest/qsqlfield.html or the "record method on QSqlDatabase":http://doc.qt.nokia.com/latest/qsqldatabase.html#record to do table introspection.

[EDIT: fixed link, volker]

[andre]

Do you really need this in your own application? I know there are third party applications that you can use for this conversion. Do you want to create such a converter yourself, or is it ok to use a third party application?

[mismail]

No, we need to convert those database in our application.
because the current version of our application uses the access database , we want to upgrade our application and use the sqlite in it.
also we want the end user just use the new version to convert the databases he already has. not downloading it again from our server.
we have more than 5000 database (books).

[andre]

Well, if it is an application specific database, I think that you know better than anyone how to transfer the data and the structure. That is going to work better than any generic approach that we can come up with. However, it seems to me that you only need to do a one-time conversion of everything, right?

[fluca1978]

Sounds to me like you need a one-shot converter from access to sqllite. But you should also have a well defined structure in access tha you need to translate in a well defined structure into sqllite, so a script or something alike to call as an external program could do the trick. And you will not need to do introspection against the original database structure.

[mismail]

Andre,
Yes it is one time conversion.

[andre]

Then, why do you need it in your own application? In that case, any MDB to SQLite conversion will do, right? Even better if you can script it.