mORMot Open Source

JD

Member

Registered: 2015-08-20

Posts: 101

Bulk insert to tables

As part of an effort to slowly migrate an application to the mORMot ORM, I have to perform bulk inserts to many tables after creating the tables.
All my setup code is in a HUGE SQL file that I use for preparing the tables in the database. The INSERT SQL for the country table above puts all 193 countries of the world into the table.

I have read about the Batch procedure but I don't know how to move from pure SQL to ORM Batch without having to type every line to be added as follows

procedure TfrmServeur.InitializeTables;
var
  Country: TSQLCountry;    // TSQLCountry derives from TSQLBaseRecord which derives from TSQLRecord
begin
  DBClient := TSQLRestClientDB.Create(TSQLModel(TSQLCountry), nil, DBNAME, TSQLRestServerDB);
  //
  if DBClient.TableRowCount(TSQLCountry) = 0 then
  begin
    Country := TSQLCountry.Create;
    try
      // Add the 193 countries
      Country.Name := 'Afghanistan';
      DBClient.Add(TSQLRecord(Country), True);
      Country.Name := 'Albania';
      DBClient.Add(TSQLRecord(Country), True);
      ......
      Country.Name := 'Zimbabwe';
      DBClient.Add(TSQLRecord(Country), True);
   finally
      User.Free;
    end;
  end;
end;

Is there a better way to do this especially since some of the tables contain over 30,000 entries in the INSERT SQL?

Thanks,

JD

Last edited by JD (2016-10-29 20:21:23)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: Bulk insert to tables

documentation

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Bulk insert to tables

documentation

Thanks for the link but I still don't understand. How do I populate the object before looping through the contents?

I was thinking of something along the lines of a

LoadFromStream()
LoadFromFile('countries.sql')
LoadFromCSV('countries.csv')

and so on. Thanks a lot for your help.

Last edited by JD (2016-10-29 21:15:53)

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Bulk insert to tables

Can anyone help me, please? I just need to see a simple example where a raw INSERT SQL with say 50 inserts (a relatively small number) is converted entirely to using BatchAdd.

uian2000

Member

Registered: 2014-05-06

Posts: 68

Re: Bulk insert to tables

How about trying extract country names of SQLfile into csv format or lines.
Then you could load then using TStringList, and write loops to apply names in "Assign country;Add country" statement.