Copy table data between two different databases.

[Jeff Steinkamp 1]

I am using SQLite in this project along with some FireDAC controls.  Both databases have a table called county which is the same table, but one table has different data.  I need to be able to copy the county table data is Database B into the county table in database A after truncating that table of data.  Someone had mentioned using the BatchMove component, but after trying to research this out it became almost as convoluted as moving a shot of Scotch from the planet Uranus to a picnic table in Key West with a few trips around Jupiter!  What would be the best and easiest way to accomplish this feat?  There are around 3300 records in this table.  If I can create a solution for this, it will solve another major headache I have with my Window 7 users accessing secure data downloads that are currently unavailable to the Win 7 user due to the lack of SSL/TSL in their operating system.

[Serge_G 85]

3 hours ago, Jeff Steinkamp said:

Someone had mentioned using the BatchMove component,

Perhaps me 😊 if so, in my mind it was like this, source is Database A, dest is Database B

Source selection is a TFDQuery so you can restrict to what you need, this to respond your "truncating data"

you just have to adjust properties of FDBatchMove,  depending on process you want (only appending, appending or updating see property mode)
Note : keep in mind what you have to do with errors during the process. 3300 records is not so much.

 

to be known, fdbatchmove have a contextual menu. The 3 first options are very useful and with the first one ( Execute) debugging is easy (without running program !)

see https://docwiki.embarcadero.com/Libraries/Alexandria/en/FireDAC.Comp.BatchMove.TFDBatchMove and https://docwiki.embarcadero.com/CodeExamples/Alexandria/en/FireDAC.TFDBatchMove_Sample

 

[Uwe Raabe 1890]

If the tables have the same structure it might be as simple as using two TFDTable components with their attached connections and call CopyDataSet on the target one.

[Cristian Peța 94]

Maybe I'm wrong, but CopyDataSet doesn't use ArrayDML. Only if speed matters.

[Uwe Raabe 1890]

Regarding speed, the docwiki link states at the end:

Quote

• Assigning values to Data is much faster than CopyDataSet.

Depending on the requirements about the restrictions mentioned in the lines above that quote, assigning Data may be the better approach, while CopyDataSet covers a broader use case.

 

[Jeff Steinkamp 1]

On 11/4/2023 at 2:37 AM, Uwe Raabe said:

If the tables have the same structure it might be as simple as using two TFDTable components with their attached connections and call CopyDataSet on the target one.

I tired playing around with this and got Abstract Errors no matter what I did.  However, I did find some more down to earth instructions on how to use BatchMove and the solutions was horribly simple.  I just added this to my Data Module and it just works.  Here is the code.

procedure TDM.CloneCountry;
begin
  WinData.ExecSQL('delete from country');
  tmp.Open('select * from country');   // users table
  ddquery.Open('select * from country'); // default table

  // setup the writer
  LDataSetWriter.DataSet :=tmp;
  LDataSetWriter.Optimise := False;

  //setup the reader
  LDataSetReader.DataSet := ddquery;
  LDataSetReader.Optimise := False;

  // make it happen
  FDBatchMove.GuessFormat;
  FDBatchMove.Execute;
end;

 

[JohnLM 10]

I finally found the method I've used for this process, using this method below and is also simple and quick, just a few ms two copy a 50k source, win7 laptop. 

 

This code snippet is from a small test project I made while learning some database processes.  I have to grids, an sqlite (sqlitetable1), and a tfdmemtable (mtable2).

This copies from the sqlite db/grid to the memtabble/grid. 

  mtable2.Active:=false; 
  mtable2.FieldDefs.Assign(SqliteTable1.FieldDefs);
  mtable2.Data:=SqliteTable1.Data;
  mtable2.Active:=true;

 

 

Edited November 6, 2023 by JohnLM
typos