insert or ignore

[sandy771]

I am inserting a large number of records into a database 1000 at a time within a transaction using

UniQuery->Append();
...
UniQuery->Post()

I am using UniQuery->FieldByName(...) as a few of the fields are largeish blobs

One of the fields should contain unique values and has a unique constraint and I want to use the equivalent of INSERT OR IGNORE.

Is there an easy way to go about this. At the moment I am inserting all of the rows and thende-duplicating, which I expect is slower than it could be. I expect that there are better was of doing this.

[AlexP]

Hello,

If your database supports the INSERT OR IGNORE construct, you can set your own INSERT query with this construct in the UniQuery.SQLInsert property. If the DB doesn't support this functionality, you can use the EDAError.IsKeyViolation property in the following way:

Code: Select all

begin
  UniQuery1.Append;
  UniQuery1.FieldByName('DEPTNO').AsInteger := 40;
  UniQuery1.Post;
  UniQuery1.Append;
  UniQuery1.FieldByName('DEPTNO').AsInteger := 40;
  try
    UniQuery1.Post;
  Except
    on e: EUniError do
      if not EDAError(E).IsKeyViolation then
        Raise
      else
        UniQuery1.Cancel;
  end;
end;

[sandy771]

Thanks Alex

[AlexP]

Hello,

If you have any other questions, feel free to contact us.

[sandy771]

Hi Alex

Is there a memthod of doing an insert or ignore when loading a table via a tuniloader?

Cheers
Paul

[AlexP]

Hello,

Unfortunately, for the time being, there is no such capability when using UniLoader. We will consider the possibility to add an onError event to UniLoader in one of the next versions.