Constructing an SQL query for importing via ODBC

After you access your ODBC data source, use the SQL Query builder to construct and execute a query. Using the SQL Query builder, you construct queries that can search an ODBC data source or DBMS, fetch specific records, and import the resulting records into a FileMaker Pro database file.

You can execute the query immediately, or store it in a field for repeated use. See Storing an SQL query in a field for details.

Note  Performing complex queries or retrieving a large number of records may take time. Consider batching requests for optimal performance.

To construct a query in the SQL Query builder:

  1. Choose your data source.
    • If you’re importing into an existing FileMaker Pro file, choose File menu > Import Records > ODBC Data Source, and then choose your data source. The SQL Query builder dialog box appears (you might see a password and user name dialog box first).
    • If you’re creating a FileMaker Pro file from the data source records, choose File menu > Open. In the Open File dialog box, locate the file. For Files of type (Windows) or Show (macOS), choose ODBC Data Source, then choose your data source. The SQL Query builder dialog box appears (you might see a password and user name dialog box first).
  2. In the SELECT tab > Tables list box, click the table you want to import. The columns associated with this table appear in the Columns list box.
  3. Choose a column to insert into your SQL query and click Insert into SQL Query.

    This constructs the SQL statement in the SQL Query box.

  4. Insert additional columns into the SQL query by double-clicking the column name.
  5. Click the WHERE tab to construct search criteria. This reduces the number of records that are imported. You can also join data from two tables.
  6. To sort records before importing, click the ORDER BY tab, then specify the name of the column to sort by and whether the sort should be ascending or descending.
  7. Execute the query.

    You can map fields, choose whether to append records or create a table, or browse records in the data source to decide which records you want to import. FileMaker Pro imports the resulting records into your database file.

Notes 

  • You can type an SQL statement directly into the SQL Query box. However, to assure a valid SQL query, use the SQL Query builder.

Related topics