Copy Database Table

VRonin

I need to copy a table from a MS SQL server to a newly created SQLite file. What's the easiest way to go around this?

I'm trying to avoid the select-> iterate over records -> insert every single record to the new DB

[Moved to 3rd Party Software ~kshegunov]
[Moved back to General and Desktop ~kshegunov]

kshegunov

Okay. 1) and 2) you can do in a single pass. I'd create a "template" SQLite database that I'd put in a resource file. Then when it's required I'd just copy the SQLite database file (along with the prepared structure inside of it) to a normal file. Then I'd open the database the usual way and proceed with the data export. The data export would go like this:

open both databases simultaneously
begin a transaction in the MSSQL database so it doesn't get modified in the meantime
begin a transaction in SQLite so we can get optimization(s) for the inserts
select from MSSQL and insert each row I get into the SQLite (you could use unnamed bindings, i.e. with indexes, if you can ensure the structure is the same.
commit the transaction in the SQLite, rollback the one in MSSQL

I know of no better way than to do this in C++ code, unfortunately.

kshegunov

I think the most painless way is to export it into a SQL text file (disabling foreign key checks and such) and then reimport it into the SQLite, at least that's what I'd always done in such a situation. You can get a full DB dump from the MS SQL console/tool you're using, right?

VRonin

Sorry, forgot to mention, this is not a one off, my Qt C++ program must be able to do the copy "uppon the press of a button"

kshegunov

Oh, sorry for the move then. ^_^

I see, then what about the following:
You move the target table inside the SQLite database if it exists already, so you'd get a backup. Then you create it anew based on the structure you could get from the one in the MSSQL, then lock it (in SQLite), import the data you get from MSSQL, and finally unlock it?
I don't believe Qt provides any special means of exporting/importing tables through the various drivers (I may be wrong), so I'd say it's up to you to do it manually.

VRonin

The process would be:

crate a new SQLite db
create a table (structure is known and fixed)
do the equivalent of INSERT into NewTable select * from OldTable, but with NewTable and OldTable living in 2 databases in 2 different "servers" (quotes as SQLite doesn't have a server)

kshegunov

Okay. 1) and 2) you can do in a single pass. I'd create a "template" SQLite database that I'd put in a resource file. Then when it's required I'd just copy the SQLite database file (along with the prepared structure inside of it) to a normal file. Then I'd open the database the usual way and proceed with the data export. The data export would go like this:

open both databases simultaneously
begin a transaction in the MSSQL database so it doesn't get modified in the meantime
begin a transaction in SQLite so we can get optimization(s) for the inserts
select from MSSQL and insert each row I get into the SQLite (you could use unnamed bindings, i.e. with indexes, if you can ensure the structure is the same.
commit the transaction in the SQLite, rollback the one in MSSQL

I know of no better way than to do this in C++ code, unfortunately.