SQL INSERT with values from Arrays

When several records must be copied or imported in FileMaker, the traditional solution often uses script steps:

1. Navigate to a layout based on the source table.
2. Find the source records.
3. Export, import, or loop through the found set.
4. Navigate to a layout based on the destination table.
5. Create and populate the new records.
6. Restore the original layout and found set.

This approach requires layout changes and management of FileMaker's current context. It can also require several script variables and one iteration for every record.

ACF can perform the same operation directly with SQL and typed arrays. The complete operation normally consists of:

1. One SELECT ... INTO statement that retrieves the source rows into arrays.
2. One INSERT statement containing all the destination rows.

No layout navigation, found-set manipulation, export, import, or per-record insert loop is required.

Building SQL VALUES from arrays

From the ACF-Plugin 1.8.0.6 and later, the following functions convert parallel ACF arrays into the row tuples used after the SQL VALUES keyword:

string values = MakeSQLValues(array1, array2, array3);
string valuesFM = MakeSQLValuesFM(array1, array2, array3);

Use:

• MakeSQLValues for 4D SQL, MySQL, and SQLite.
• MakeSQLValuesFM for FileMaker SQL.

MakeSQLValuesFM adds the FileMaker SQL prefixes required for typed date and time values:

DATE '2026-01-01'
TIME '14:30:00'
TIMESTAMP '2026-01-01 14:30:00'

Strings are enclosed in single quotes, and embedded single quotes are escaped according to SQL rules. Numbers and Boolean values are not quoted.

All arrays must have the same number of elements. Each array represents one destination column, and elements with the same array index form one destination record.

For example, four arrays containing three elements produce:

('Product A', 234, DATE '2026-01-01', 400.55),
('Product B', 768, DATE '2026-01-05', 36.5),
('Product C', 916, DATE '2026-07-13', 200.5)

Example: Copy offer descriptions to an order

Assume that an offer has been converted to a new order. The related records in OfferDescriptions must now be copied to OrderDescriptions.

The new Order ID can be included as a literal column in the SELECT. FileMaker SQL returns that value once for every selected source record, so the orderID array is immediately ready for the subsequent insert.

function CopyOfferDescriptionsToOrder(string offerID, string newOrderID)

    array string orderID, description;
    array int sorting;
    array float price;

    string result = ExecuteSQL(
        "SELECT
            :newOrderID,
            Description,
            Sorting,
            Price
         FROM OfferDescriptions
         WHERE OfferID = :offerID
         ORDER BY Sorting
         INTO :orderID,
              :description,
              :sorting,
              :price"
    );

    if (sizeof(description) > 0) then
        result = ExecuteSQL(
            "INSERT INTO OrderDescriptions
                (OrderID, Description, Sorting, Price)
             VALUES " +
            MakeSQLValuesFM(orderID, description, sorting, price)
        );
    end if

    return result;
end

The function performs one SQL read and one multi-row SQL insert. There is no loop that assigns array values to scalar variables and no separate INSERT for every description record.

The order of the arrays passed to MakeSQLValuesFM must match the order of the columns in the INSERT statement:

Copying additional columns

Additional destination columns can be prepared directly in the SELECT. A column can originate from:

• A source table field.
• A joined table.
• An ACF placeholder such as :newOrderID.
• A SQL literal or calculated expression.

Declare one correctly typed array for each selected column and add the same array to the INTO list and the MakeSQLValuesFM call.

This makes it possible to prepare a complete destination dataset in the initial query without processing every row afterward.

Paging large datasets

Very large arrays can produce an SQL statement that exceeds a practical statement size. The paged form limits the number of generated rows:

string values = MakeSQLValuesFM(
    pageNo,
    pageSize,
    orderID,
    description,
    sorting,
    price
);

Page numbers are 1-based. With a page size of 100:

• Page 1 returns array rows 1–100.
• Page 2 returns array rows 101–200.
• The final page returns the remaining rows.
• A page after the final row returns an empty string.

int pageNo = 1;
int pageSize = 100;
string values, result;

repeat
    values = MakeSQLValuesFM(
        pageNo,
        pageSize,
        orderID,
        description,
        sorting,
        price
    );

    if (values != "") then
        result = ExecuteSQL(
            "INSERT INTO OrderDescriptions
                (OrderID, Description, Sorting, Price)
             VALUES " + values
        );
    end if

    pageNo++;
until (values == "");

The loop operates once per page rather than once per record.

Benefits compared with FileMaker import script steps

• No navigation between layouts.
• No dependency on the current found set.
• No temporary export or import file.
• No per-record insert loop.
• No need to preserve and restore the user's current layout context.
• Source values can be combined with new foreign keys and other literals in the initial query.
• Typed ACF arrays determine the correct SQL representation of each destination column.

This pattern is particularly useful when converting offers to orders, copying templates, duplicating related line items, importing API results, or transferring records between supported SQL data sources.