如果工程新增或去掉了一些字段,想把旧表的数据迁移到新表上来
qt c++代码
//先获取新表的所有字段
QSqlQuery query;
query.prepare(" PRAGMA table_info('tablename')");
if(query.exec()){
while(query.next()){
ColCountList.push_back(query.value(1).toString());//获取drugdata里的所有字段
strCols.append(query.value(1).toString() + ",");//组成语句
}
}
bool ExecuState = true;
//创建临时表并拷贝数据
QString strCreateTemp_table = "CREATE TABLE temp_table AS SELECT * FROM tablename;";
QSqlQuery qSqlQuery(qSqlDatabase);
if (!qSqlQuery.exec(strCreateTemp_table))
{
qWarning() << "strCreateTemp_table error: " << qSqlQuery.lastError().text();
ExecuState = false;
}
//删除旧表
QString strDropDrugData = "DROP TABLE tablename;";
if (!qSqlQuery.exec(strDropDrugData))
{
qWarning() << "error: " << qSqlQuery.lastError().text();
ExecuState = false;
}
//创建新表
if (!qSqlQuery.exec(strCreateTable))
{
qWarning() << "strCreateTable error: " << qSqlQuery.lastError().text();
ExecuState = false;
}
strCols = strCols.left(strCols.count() - 1);
//迁移数据
QString strMigrateData = QString("INSERT INTO tablename ( %1 ) SELECT %2 FROM temp_table;").arg(strCols).arg(strCols);
if (!qSqlQuery.exec(strMigrateData))
{
qWarning() << "strMigrateData error: " << qSqlQuery.lastError().text();
ExecuState = false;
}
//删除临时表
QString strDropTemp_table = "DROP TABLE temp_table";
if (!qSqlQuery.exec(strDropTemp_table))
{
qWarning() << "strDropTemp_table error: " << qSqlQuery.lastError().text();
}
if(ExecuState)
{
qDebug()<<"Migrate Data complete!";
}