AnsweredAssumed Answered

SQL FETCH FIRST does not work with INSERT/SELECT

Question asked by dansmith65 on Sep 19, 2014
Latest reply on Sep 22, 2014 by TSGal

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

Outcomes