1

I want to insert data to a nexusdb table which are in a access database table. i am using two query for this. one for select data from access database and other one for insert to nexusdb. I don't want insert this using a loop. Is there any way to insert all selected dataset directly?

RBA
  • 12,337
  • 16
  • 79
  • 126
Ishanka
  • 360
  • 4
  • 18

1 Answers1

1

Is there any way to insert all selected dataset directly?

If the two databases were on, say, MS Sql Servers or some other back-end Sql servers like MySql, you could do this, but between MS Access and NexusDB, none that I know of. The only db access connectors they seem to have in common is ODBC, and afaik that doesn't support heterogeneous queries across different drivers (so that you can't f.i. do an "insert into destinationserver.database.table select * from source.database.table")

So I think you're stuck with a loop. The only real question is whether you have to write the loop yourself or whether it's hidden in library code.

The code below uses FireDAC's TFDDataMove component to move data between tables. Of course, to use it requires the destination server to support either FireDAC or ODBC. As Uwe Raabe says, as of now (Delphi current version being Seattle) FireDAC does not support NexusDB or vice versa. However, there is an ODBC driver available for NexusDB, according to their website. Although I don't have NexusDB installed, the following works fine with another ODBC driver, so ought to work with NexusDB's.

procedure TForm1.TestDataMove;
var
  Item : TFdMappingItem;
begin
  Item := FDDataMove1.Mappings.Add;
  Item.SourceFieldName := 'ID';
  Item.DestinationFieldName := 'ID';

  Item := FDDataMove1.Mappings.Add;
  Item.SourceFieldName := 'Name';
  Item.DestinationFieldName := 'Name';

  FDDataMove1.Source := FDTable1;
  FDDataMove1.Destination := FDTable2;
  FDDataMove1.Options := FDDataMove1.Options - [poOptimiseSrc];
  FDDataMove1.Execute;

  FDConnection2.Connected := False;
  FDTable2.Open;
end;
MartynA
  • 30,454
  • 4
  • 32
  • 73
  • As of Seattle, FireDAC doesn't support NexusDB. As the Source of that DataMove component actually can be a simple TDataSet, this approach would work if you want to move the data from the Nexus table into a FD compatible dataset, but not the other way round as requested here. – Uwe Raabe Mar 25 '16 at 10:22
  • Perhaps `TFDBatchMove` is the better approach: http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Comp.BatchMove.TFDBatchMove – Uwe Raabe Mar 25 '16 at 10:26
  • @UweRaabe: Thanks. There seems to have been a bit of a campaign to get Nexus to support FireDAC, but I don't know what came of it. – MartynA Mar 25 '16 at 10:44
  • See update, which should work with NexusDB via ODBC without needing you to write a loop. – MartynA Mar 26 '16 at 12:26