Qt - copy a table from one database to another

Question

I'm trying to copy table data from one database to another using this command:

INSERT INTO database2.table2 SELECT * from database1.table1

But I run into an error.

I have two databases called database1.db and database2.db

Here's my code:

database1_path = "/home/user/Desktop/database1.db";
database2_path = "/home/user/Documents/database2.db";

{
    QSqlDatabase db1 = QSqlDatabase::addDatabase("QSQLITE");
    db1.setDatabaseName(database1_path);

    QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE");
    db2.setDatabaseName(database2_path);

    if(!db1.open() && !db2.open())
    {
        qDebug() << "databases are NOT open!";
    }
    else
    {
        QString insert = QString("INSERT INTO '/home/user/Desktop/database1.db'.table1 SELECT * FROM '/home/user/Documents/database2.db'.table2;");

        QSqlQuery query(insert, db1);

        if(!query.exec(insert))
        {
            qDebug() << "Error:" << query.lastError().text();
        }
    }
}

And here is the error I get:

Error: "no such table: /home/user/Desktop/database1.db.table1 Unable to execute statement"

I also tried: "INSERT INTO database1.table1 SELECT * FROM database2.table2;"

and: "INSERT INTO db1.table1 SELECT * FROM db2.table2;"

But the same error occurred...

How can I solve this problem?

score 1 · Accepted Answer · answered Jan 13 '23 at 13:22

You cannot open two databases with two different connection and references the other one from another connection. For your use case you need to use the attach command in sql, so that your connection can references both databases.

ATTACH DATABASE '/home/user/Documents/database2.db' AS second_database;
INSERT INTO table1 SELECT * FROM second_database.table2;

Of course that assume that both database/table have compatible schema.

Qt - copy a table from one database to another

1 Answers1