0

I am using QSqlQuery to insert multiple entries in one query. I need to get the IDs of all elements that were inserted with my query :

QString queryContent = "INSERT INTO table (field1, field2) VALUES";
bool insertedFirstEntry = false;
for (const auto& data : dataCollection)
{
    if (insertedFirstEntry)
        queryContent += ",";
    else
        insertedFirstEntry = true;

    queryContent += QString(" ('%1', '%2')").arg(data.field1).arg(data.field2);
}

QSqlQuery query(db);
const bool result = query.exec(queryContent);

std::vector<int> insertedIds;
if (result)
{
    while (query.next())
        insertedIds.push_back(query.value(0).toInt());
}
return insertedIds;

This code is not working as query.next() is always returning false.

Qt documentation and this answer suggests using QSqlQuery::lastInsertId(), but as I have multiple entries that have been inserted, this wouldn't work for my use case.

Also, Qt documentation warns that QSqlQuery::lastInsertId() is to be used only for the case of single record inserts :

Returns the object ID of the most recent inserted row if the database supports it. An invalid QVariant will be returned if the query did not insert any value or if the database does not report the id back. If more than one row was touched by the insert, the behavior is undefined.

How can I retrieve the ids of all entries that have been inserted by my query?

Mickaël C. Guimarães
  • 1,020
  • 2
  • 14
  • 32
  • I know you don't really need a scolding right now, but this is one reason why AUTOINCREMENT fields are less than optimal. – Tim Roberts Jul 25 '23 at 22:04
  • @TimRoberts I don't understand what you mean. Can you explain what you would do / organize the database in an answer? – Mickaël C. Guimarães Jul 25 '23 at 22:33
  • Well, the principle is that, if you need to KNOW the unique ID of the row, then you should assign a unique ID in the columns you are adding -- your own primary key. It's a little late to make that change now. – Tim Roberts Jul 25 '23 at 23:32

0 Answers0