Option for inserting records: INSERT IGNORE INTO

Feature Request
#1

When using Server Connect’s Database Insert step, I would find it very helpful to be able to choose INSERT IGNORE INTO, in addition to the existing INSERT INTO.

I often have to insert a large number of IDs into a table - perhaps a join table in a many:many relationship. The target table may only consist of two IDs (a unique combination) . It doesn’t matter if the combination of IDs already exists; in this case the insert can be ignored. The important thing is the insert operation should work.

As it is, if I want to add a selection of records, and the pair of IDs already exists, the operation will fail at the point the duplicate record is encountered. The only way to get round this is to check if the record exists before making the insert. In the case of a large number of records, this makes the process more compilcated and much slower.

For the time being, I’ve modified SqlBuilder.php to get this to work, but of course this may be overwritten by Wappler at some point. Also, it would be better to be able to choose whether or not to use the IGNORE option.

Upsert
#2

Are you using the server action validate Data feature to do the checks or are you doing it with custom code?

#3

I’m not using any checks in Wappler. I’m using a composite primary key, so the validation is left to MySQL. All that’s needed is for inserts to be ignored if a duplicate would be created. I would often want this to apply when doing multiple inserts - simply done with the ‘IGNORE’ option (which I’ve enabled by editing the relevant file).

I think a problem with the validate data feature in server connect is that it checks conditions using OR, not AND. (Unless it’s the other way round. When I last thought about using it, I realised it did the one I didn’t want anyway.)

#4

The IGNORE options is not in the SQL ANSI standard, it is a MySQL only feature.

#5

Thanks - I didn’t know that. I’ll have to check for changes when new Wappler releases are made, but I don’t think SqlBuilder.php changes very often.

A list of modified files with new releases would be very helpful. This cropped up in a thread recently so hopefully will be available at some point.