Merge two Firebird databases from Delphi

Question

I'm using the Delphi Sydney version and the database is Firebird 2.5. I have two databases with identical structures, but it's not 100% the same data.

My strategy is to create two FDConnections and two FDQueries, but I don't know how to get data from one table and insert it into another without creating a TYPE and complete structure for every table and sending every field. Datasource/FDTable?

procedure TForm1.Button2Click(Sender: TObject);
begin
  Memo1.Lines.Clear;
  DM_Data.FDQryDatabase.Close;
  DM_Data.FDQryDatabase.SQL.Clear;
  DM_Data.FDQryDatabase.SQL.Add('SELECT * FROM DOCUMENTI_FAMIGLIA');
  DM_Data.FDQryDatabase.Open;
  while not(DM_Data.FDQryDatabase.Eof) do
  begin
    Memo1.Lines.Add(DM_Data.FDQryDatabase.FieldValues['FDOC_IDFAMIGLIA']);
    DM_Data.FDQryDatabase.Next;
  end;
  Memo1.Lines.Add('--------------------');
  DM_Data.FDQuerySource.Close;
  DM_Data.FDQuerySource.SQL.Clear;
  DM_Data.FDQuerySource.SQL.Add('SELECT * FROM DOCUMENTI_FAMIGLIA');
  DM_Data.FDQuerySource.Open;
  while not(DM_Data.FDQuerySource.Eof) do
  begin
    Memo1.Lines.Add(DM_Data.FDQuerySource.FieldValues['FDOC_IDFAMIGLIA']);
    // Insert
    DM_Data.FDQuerySource.Next;
  end;
end;

I would like to know how to migrate from one database to another using Delphi objects making a shorter solution. FDQryDatabase will be the primary database, FDQuerySource will be added to FDQryDatabase. I don't have a problem doing table-by-table.

Some tables have 17 thousand of data; I will need to include them without duplicate data and this process runs every day until the system migrates to another (I don't know when).

Answer 1

You can add a flag to know what you already move :

procedure TForm1.Button1Click(Sender: TObject);
var
  LList: TList<string>;
  i: integer;
begin
  LList := TList<string>.Create;

  try
    FDQryDatabase.Close;
    FDQryDatabase.SQL.Clear;
    FDQryDatabase.SQL.Add('SELECT * FROM DOCUMENTI_FAMIGLIA');
    FDQryDatabase.Open;
    while not FDQryDatabase.Eof do
    begin
      LList.Add(FDQryDatabase.FieldValues['FDOC_IDFAMIGLIA']);
      FDQryDatabase.Next;
    end;

    FDQuerySource.SQL.Clear;
    for i := 0 to LList.Count - 1 do
    begin
      if i mod 500 = 0 then
      begin
        FDQuerySource.ExecSQL;
        FDQuerySource.SQL.Clear;
      end;

      FDQuerySource.SQL.Add('INSERT INTO DOCUMENTI_FAMIGLIA (FDOC_IDFAMIGLIA)');
      FDQuerySource.SQL.Add(' SELECT ' + LList.Items[i]);
      FDQuerySource.SQL.Add(' FROM DOCUMENTI_FAMIGLIA');
      FDQuerySource.SQL.Add(' WHERE NOT EXISTS (SELECT 1 FROM DOCUMENTI_FAMIGLIA df WHERE df.FDOC_IDFAMIGLIA = ' +  LList.Items[i] + ');');
    end;

    if FDQuerySource.SQL.Count > 0 then
      FDQuerySource.ExecSQL;
  finally
    FDQryDatabase.Close;
    FreeAndNil(LList);
  end;
end;