Solved Copy Database Table
-
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] -
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.
-
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?
-
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"
-
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. -
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 withNewTable
andOldTable
living in 2 databases in 2 different "servers" (quotes as SQLite doesn't have a server)
-
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.