insert new record from TFDquery into another sqlite db (firemonkey)

Question

I have 2 SQLite databases. I want to take several records from one and insert them into the other. What is a good way of doing this? I'm showing question marks below where I'm not sure what I need...

TFDQuery *queryNEWDATA;
queryNEWDATA = new TFDQuery(NULL);
queryNEWDATA->Connection = Form1->FDConnection1;   
queryNEWDATA->SQL->Text = "SELECT * FROM mtgs WHERE status = 4";  
queryNEWDATA->Open();
while(!queryNEWDATA->Eof) 
{
  TFDQuery *queryUPDATE;
  queryUPDATE = new TFDQuery(NULL);
  queryUPDATE->Connection = Form1->FDConnection2; 

  queryUPDATE->SQL->Text = ????????????????

  queryUPDATE->ExecSQL();
  queryUPDATE->Close();
  queryUPDATE->DisposeOf();
}

If my queryNEWDATA returns 4 records, I want to just push them into the other database with queryUPDATE. Note that both databases have the exact same structure and column order.

Answer 1

I don't know if this is the best way, but try something like this:

TFDQuery *queryNEWDATA = new TFDQuery(NULL);
queryNEWDATA->Connection = Form1->FDConnection1;   
queryNEWDATA->SQL->Text = _D("SELECT * FROM mtgs WHERE status = 4");
queryNEWDATA->Open();

if (!queryNEWDATA->Eof) 
{
    TStringList *FieldNames = new TStringList;
    TStringList *ParamNames = new TStringList;

    int FieldCount = queryNEWDATA->Fields->Count;
    for (int i = 0; i < FieldCount; ++i)
    {
        String FieldName = queryNEWDATA->Fields->Fields[i]->FieldName;
        FieldNames->Add(FieldName);
        ParamNames->Add(_D(":P") + FieldName);
    }

    TFDQuery *queryUPDATE = new TFDQuery(NULL);
    queryUPDATE->Connection = Form1->FDConnection2;     
    queryUPDATE->SQL->Text = _D("INSERT INTO mtgs (") + FieldNames->CommaText + _D(") VALUES (") + ParamNames->CommaText + _D(")");

    FieldNames->DisposeOf();
    ParamNames->DisposeOf();

    for (int i = 0; i < FieldCount; ++i)
    {
        TField *f = queryNEWDATA->Fields->Fields[i];
        queryUPDATE->Params->ParamByName(_D("P") + f->FieldName)->DataType = f->DataType;
    }

    queryUPDATE->Prepare();

    do
    {
        for (int i = 0; i < FieldCount; ++i)
        {
            TField *f = queryNEWDATA->Fields->Fields[i];
            queryUPDATE->Params->ParamByName(_D("P") + f->FieldName)->Value = f->Value;
        }

        queryUPDATE->ExecSQL();
        queryNEWDATA->Next();
    }
    while (!queryNEWDATA->Eof);

    queryUPDATE->Close();
    queryUPDATE->DisposeOf();
}

queryNEWDATA->Close();
queryNEWDATA->DisposeOf();