Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

SQL.InsertOrUpdateRecords

Creates or updates records in FileMaker database for current recordset.

Component Version macOS Windows Linux Server iOS SDK
SQL 7.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "SQL.InsertOrUpdateRecords"; Command; InsertFileName; InsertTableName; FieldNames; PrimaryKeyDB1; PrimaryKeyFM1 { ; SecondaryKeyDB2; SecondaryKeyFM2; TertariyKeyDB3; TertariyKeyFM3 } )   More

Parameters

Parameter Description Example Flags
Command The command reference number gained with SQL.NewCommand. $Command
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"
PrimaryKeyDB1 The name of the primary key in the record set.
Must be one of the columns in the record from SQL.
If empty, we use primaryKeyFM1 here.		 "RowID"
PrimaryKeyFM1 The name of the primary key for the update statements.
Must be one of the columns in the FileMaker table.
If empty, we use primaryKeyDB1 here.		 "RowID"
SecondaryKeyDB2 The name of the secondary key in the record set.
Must be one of the columns in the record from SQL.
If empty, we use primaryKeyFM2 here.		 "ClientID" Optional

Added in version 8.1.
SecondaryKeyFM2 The name of the secondary key for the update statements.
Must be one of the columns in the FileMaker table.
If empty, we use primaryKeyDB2 here.		 "ClientID" Optional

Added in version 8.1.
TertariyKeyDB3 The name of the tertary key in the record set.
Must be one of the columns in the record from SQL.
If empty, we use primaryKeyFM3 here.		 "CompanyID" Optional

Added in version 8.1.
TertariyKeyFM3 The name of the tertary key for the update statements.
Must be one of the columns in the FileMaker table.
If empty, we use primaryKeyDB3 here.		 "CompanyID" Optional

Added in version 8.1.

Result

Returns list or error.

Description

Creates or updates records in FileMaker database for current recordset.
This function allows to easily copy a lot of records from SQL database into FileMaker table.
Please provide field names in FileMaker. The order has to match those in the record set.
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.
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.
This will not work for BLOBs as they are stored currently as hex encoded text only.

Function will fail if data types in FileMaker and other SQL database do not match for assignment. e.g. date and time fields will not work, only timestamp fields. Fixed in 10.2, so it may work now.

For copying record within FileMaker, please use FM.InsertRecordQuery or FM.SQL.InsertRecords functions.

Returns list with three values: Number of records inserted, number of records updated and number of errors.
Primary key in FM must be defined as unique and validate always, so FileMaker properly reports a duplicate.

We first do a select to count how many we have and then do either insert or update. If insert returns duplicate error, we try update, too. Up to ten pairs of DB and FM keys are possible.

This function requires working indexes in the table. And don't overload the function, so better work in blocks of e.g. 1000 rows at a time.

Examples

Insert or update records:

# get connection before.
# Create select and run it
Set Variable [ $Command ; Value: MBS("SQL.NewCommand"; $Connection; "SELECT RowID, FirstName, LastName, TotalSales, NumberOfOrders FROM Test") ]
# Run it
Set Variable [ $result ; Value: MBS("SQL.Execute"; $Command) ]
If [ $result ≠ "OK" ]
    Show Custom Dialog [ "Error: " & $result ]
Else
    Set Variable [ $result ; Value: MBS("SQL.InsertOrUpdateRecords"; $Command; Get(FileName); "SQLite fun"; "PKey¶FirstName¶LastName¶TotalSales¶NumberOfOrders"; "RowID"; "PKey") ]
    Show Custom Dialog [ "Records imported." & ¶ & ¶ & $result ]
End If
# Cleanup
Set Variable [ $result2 ; Value: MBS("SQL.FreeCommand"; $Command) ]
Set Variable [ $result2 ; Value: MBS("SQL.Commit"; $Connection) ]
Set Variable [ $result2 ; Value: MBS("SQL.FreeConnection"; $Connection) ]

See also

Release notes

  • Version 16.1
    • Added FM.SQL.InsertOrUpdateRecords and Matrix.InsertOrUpdateRecords functions.
    • Added FM.SQL.InsertOrUpdateRecordsToSQL and Matrix.InsertOrUpdateRecordsToSQL functions.
  • Version 13.0
  • Version 12.0
  • Version 10.2
    • Changed SQL functions to return SQL date/time/timestamp to FileMaker in that type and not always as timestamp. This may help to get SQL.InsertOrUpdateRecords to work with time, date and timestamp.
  • Version 8.1
  • Version 7.3

Blog Entries

This function checks for a license.

Created 21st June 2017, last changed 15th October 2021


SQL.GetRecordsAsText - SQL.InsertRecords