Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server (Not)   Client   Old   Guides   Examples
New in version: 6.2   6.3   6.4   6.5   7.0   7.1   7.2   7.3   7.4   7.5    Statistic  

FM.InsertRecordQuery

Inserts a lot of records queried from a database.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
FM FMSQL 5.1 Yes Yes Yes Yes Yes

MBS( "FM.InsertRecordQuery"; InsertFileName; InsertTableName; FieldNames; QueryFileName; SQL Statement { ; Params } )

Parameters

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.
"Model"
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.
123

Result

Returns OK or error.

Description

Inserts 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.
Does not copy text styles, only plain text for text fields.
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.

Using FM.SetSQLBatchMode you can let the plugin insert e.g. 50 rows in one chunk which can speed up insert a lot.
Due to memory usage it can be useful to segment copy queries to only copy one GB at a time and avoid heavy memory pressure.

With plugin version 7.2 or later, 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.

Examples

Copy records within one table in same file:

MBS( "FM.InsertRecordQuery"; ""; "Contacts"; "First¶Last¶Company"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )

Copy records from a table from one database to other (production to development)

MBS( "FM.InsertRecordQuery"; "ContactsDev.fmp12"; "Contacts"; "First¶Last¶Company"; "Contacts.fmp12"; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )

Copy data to other table:

MBS( "FM.InsertRecordQuery"; ""; "Test"; "FirstName¶LastName¶CompanyName"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts" )

Query sum of sales grouped by customers in a sales report table:

MBS( "FM.InsertRecordQuery"; ""; "SalesReport"; "SumSales¶CustomerID"; ""; "SELECT sum(\"InvoiceTotal\"), \"CustomerID\" FROM Clients, Invoices WHERE Invoices. CustomerID = Clients.CustomerID GROUP by CustomerID" )

Copy two fields from one table to other:

MBS( "FM.InsertRecordQuery"; ""; "ASC Apple Script"; "Name¶Script"; ""; "select * from \"AppleScript\"")

Copy from Anlagen example (in German) to copy of same file:

MBS( "FM.InsertRecordQuery";
/* InsertFileName */
"Anlagen2";
/* InsertTableName */
"Anlagen";
/* FieldNames */
"Abschreibung Jahre¶Anzahl Diagramme¶Ausgewählte Datei¶Datei 1 Container¶Datei 2 Container¶Datei 3 Container¶Datei 4 Container¶Datei 5 Container¶Datum Ausgang¶Datum Ausgang Popover¶Datum Eingang¶Erstellungsdatum¶Fälligkeit¶Fälligkeit Popover¶Gekauft von¶Geprüft durch¶In Betrieb seit¶Interne ID¶Kategorie¶Kaufdatum¶Kosten¶Modell¶Notizen¶Ort¶Position¶Prüfdatum¶Seriennummer¶Signatur Container¶Zugewiesen an¶Zustand";
/* QueryFileName */
"Anlagen";
/* SQL Statement */
"SELECT \"Abschreibung Jahre\", \"Anzahl Diagramme\", \"Ausgewählte Datei\", \"Datei 1 Container\", \"Datei 2 Container\", \"Datei 3 Container\", \"Datei 4 Container\", \"Datei 5 Container\", \"Datum Ausgang\", \"Datum Ausgang Popover\", \"Datum Eingang\", \"Erstellungsdatum\", \"Fälligkeit\", \"Fälligkeit Popover\", \"Gekauft von\", \"Geprüft durch\", \"In Betrieb seit\", \"Interne ID\", \"Kategorie\", \"Kaufdatum\", \"Kosten\", \"Modell\", \"Notizen\", \"Ort\", \"Position\", \"Prüfdatum\", \"Seriennummer\", \"Signatur Container\", \"Zugewiesen an\", \"Zustand\" FROM Anlagen"
)

Copy data to other table with parameter:

MBS( "FM.InsertRecordQuery"; ""; "Test"; "FirstName¶LastName¶CompanyName"; ""; "SELECT \"First\", \"Last\", \"Company\" FROM Contacts WHERE City=?"; "Hamburg" )

See also


FM.InsertRecordCSV   -   FM.InsertRecordQueryIgnoreDuplicates

Feedback: Report problem or ask question.




Links
MBS Xojo Plugins