Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 7.5   8.0   8.1   8.2   8.3   8.4   8.5   9.0   9.1   9.2    Statistic  


Insert or updates a lot of records queried from a database.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
FM FMSQL 8.4 Yes Yes Yes Yes Yes
MBS( "FM.InsertOrUpdateRecordQuery"; InsertFileName; InsertTableName; FieldNames; KeyFieldMapping; QueryFileName; SQL Statement { ; Params } )   More


Parameter Description Example value
InsertFileName The file name of where the insert table is inside. Can be empty to look for the table in all files. Get(FileName)
InsertTableName The name of the table to insert record into. "Assets"
FieldNames A list of field names for the insert.
Empty entries in the list are ignored.
KeyFieldMapping The list of field names with index to map keys.
Please specify a list of key name=key index.
The key name is the name of the field in the target table and the index gives zero-based position in result set from query.
QueryFileName The file name for the database to run the query against. Can be empty to look for the table in all files. Get(FileName)
SQL Statement SQL Statement as a Text string
Params Optional
Pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.


Returns OK or error.


Insert or updates a lot of records queried from a database.
This function allows to easily copy a lot of records from one table to another table.
Copy can be from one database to other or within same database. Also from one table to other or within a table. Due to passing in new field names, you can even rearrange values from one column to other while copying. In the SQL you can use functions for sums or join data from several tables together to fill a new table. Filename parameters can be empty.
Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message.
Reports an error if field name list doesn't match column count of the query result.

Please remove from SQL statement and field names all fields which are globals or unstored calculations.

For inserting records from other database (e.g. MySQL), please use SQL.InsertRecords function.

You can specify fields and table via IDs and the plugin will lookup them for you at runtime. Table IDs and table names must be unique across all open files for this to work correctly.

You can use GetFieldName() function to query field names and the plugin removes the table prefix with :: in the field name before passing it to SQL engine.


Copies records from Contacts to ContactsCopy:

Set Variable [ $r ; Value: MBS( "FM.InsertOrUpdateRecordQuery"; ""; "ContactsCopy"; "ID¶First¶Last¶Mobile"; "ID=0"; ""; "SELECT \"ID\", \"First\", \"Last\", \"Mobile\" FROM \"Contacts\"" ) ]

See also

Created 27th July 2018, last changed 10th April 2019

FM.InsertOrUpdateRecord3   -   FM.InsertRecord

Feedback: Report problem or ask question.

MBS Xojo PDF Plugins