Copy Table from SQLite to MySQL or other DB Type

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Copy Table from SQLite to MySQL or other DB Type

Post by mrcoollion » Thu May 04, 2017 6:29 pm

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

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Copy Table from SQLite to MySQL or other DB Type

Post by mrcoollion » Fri May 05, 2017 4:32 pm

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

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Copy Table from SQLite to MySQL or other DB Type

Post by mrcoollion » Fri May 05, 2017 6:11 pm

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.

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
Regards,

Paul

Post Reply

Return to “Databases”