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 Available in MBS FileMaker Plugin 8.1 or newer.
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
SecondaryKeyFM2 Available in MBS FileMaker Plugin 8.1 or newer.
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
TertariyKeyDB3 Available in MBS FileMaker Plugin 8.1 or newer.
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
TertariyKeyFM3 Available in MBS FileMaker Plugin 8.1 or newer.
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

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

Blog Entries

This function checks for a license.

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


SQL.GetRecordsAsText - SQL.InsertRecords