6 Replies Latest reply on Sep 22, 2014 2:17 PM by TSGal

    SQL FETCH FIRST does not work with INSERT/SELECT

    dansmith65

      Summary

      SQL FETCH FIRST does not work with INSERT/SELECT

      Product

      FileMaker Pro

      Version

      13.0v3

      Operating system version

      Windows 7 Pro SP1

      Description of the issue

      I'm using BaseElements/SQL Runner to select files from one table and insert them into another table in a single call (insert into ... select ... from). This works fine as-is, but as soon as I add "fetch first # rows only" to the select statement, it fails.

      I've tried the select statement with fetch first on it's own (no insert into), and it worked fine. It seems to me that the "fetch first" option is not compatible with insert into, but this is not mentioned anywhere in the documentation.

      Steps to reproduce the problem

      INSERT INTO "new_FeeCode" ( "code", "creationAccount", "creationHostTimestamp", "description", "id", "isBuildingFee", "modificationAccount", "modificationHostTimestamp" )
      SELECT "code", "creationAccount", "creationTimestamp", "description", "id", "isBuildingFee", "modificationAccount", "modificationTimestamp"
      FROM "old_FeeCode"
      FETCH FIRST 10 ROWS ONLY

      Expected result

      Create 10 records.

      Actual result

      Error

      Exact text of any error message(s) that appear

      SQL Runner reports this error:
      failed with FileMaker error code 8310: ERROR: FQL0001/(1:332): There is an error in the syntax of the query.

      Workaround

      Don't use FETCH FIRST with SELECT/INSERT