I am having trouble to find out how best i can copy the data from an SQLite table to an identical MySQL or other DB Type table.
I want to run through the template database one table and one record at a time and copy the complete data of this table into an identical table in the target database.
So the questions are:
1) How can I go through all the records of a table until the end and get the data for each record in a variable.
2) How can i save the retrieved record data into an identical table
3) Is it possible to do this in one 'easy' routine ?
Anybody an idea?
Regards,
Paul
Copy Table from SQLite to MySQL or other DB Type
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 720
- Joined: Thu Sep 11, 2014 1:49 pm
- Location: The Netherlands
-
- Posts: 720
- Joined: Thu Sep 11, 2014 1:49 pm
- Location: The Netherlands
Re: Copy Table from SQLite to MySQL or other DB Type
Because my question does not have an answer yet i first like to focus on the following:
How can i get the first record of a table in a SQLite DB.
And how can i get the second record and so on until the last record is reached?
Can i use revMoveToFirstRecord recordSetID and revMoveToNextRecord recordSetID for this and how without reading all records into memory?
Regards,
Paul
How can i get the first record of a table in a SQLite DB.
And how can i get the second record and so on until the last record is reached?
Can i use revMoveToFirstRecord recordSetID and revMoveToNextRecord recordSetID for this and how without reading all records into memory?
Regards,
Paul
-
- Posts: 720
- Joined: Thu Sep 11, 2014 1:49 pm
- Location: The Netherlands
Re: Copy Table from SQLite to MySQL or other DB Type
FYI: Found out a way how to go through all records of the database.
I am using the rowid column which sqlite automatically generates with each new record.
So with the following (without error catch routines) I can get all table names, how many tables, the column names for each table and how many records and the unique rowid's for each record.
With that information I can loop through the tables and records to get the data of each record.
I still have to write the data into the new (identical table in MySQL). After this has succeeded I will update this thread.
Regards,
Paul
I am using the rowid column which sqlite automatically generates with each new record.
So with the following (without error catch routines) I can get all table names, how many tables, the column names for each table and how many records and the unique rowid's for each record.
With that information I can loop through the tables and records to get the data of each record.
I still have to write the data into the new (identical table in MySQL). After this has succeeded I will update this thread.
Code: Select all
put revDatabaseTableNames(tTemplateDBConnectionID) into tDBTableNames
put the number of lines of tDBTableNames into tNumberOfTables
repeat with tTableNbr =1 to tNumberOfTables
put line tTableNbr of tDBTableNames into tTableName
put "SELECT COUNT(*) FROM " & tTableName into tSQL -- query the record count
put revDataFromQuery(,,tTemplateDBConnectionID, tSQL) into tRecordCount
put revDatabaseColumnNames(tTemplateDBConnectionID, tTableName) into tColumnNames
put "SELECT rowid FROM "&tTableName&" ORDER BY rowid" into tQueryCommand
put revDataFromQuery(,,tTemplateDBConnectionID,tQueryCommand) into tAllRowIDs
repeat with tRecordNumber = 1 to tRecordCount
put line tRecordNumber of tAllRowIDs into tRecordIdToGet
put "SELECT * FROM "&tTableName&" WHERE rowid ="&tRecordIdToGet into tQueryCommand
put revDataFromQuery(,,tTemplateDBConnectionID,tQueryCommand) into tRecordData
## Here I need to write the code to put the record data into the new identical table in MySQL
end repeat
end repeat
## Close the databases connections
revCloseDatabase tTemplateDBConnectionID
revCloseDatabase tTargetDBConnectionID
Paul