It's possible via a plugin, or if you setup an ODBC connection to your filemaker database so you can use the Execute SQL script step.
I believe the free base elements plugin supports it:
Otherwise until filemaker augments the ExecuteSQL() function to allow for an INSERT statement, you're stuck with SELECT statements.
As Mike said, Plugins are the way to go and there are quite a few plugins that add full SQL functionality to FM. In fact, most all of the major plugin developers have a SQL plugin mostly because it is easy to do and FM already supports the SQL engine.
The issue often is getting the clients to have the plugin to do the SQL. What I do with most of my clients is make sure I have a SQL plugin on the FileMaker Server and I use PSoS to run the SQL on the Server. This means the client machines don't need the plugin. And there is an added benefit that most SQL calls on the server are handled faster than via plugin on a local client.
I know everyone has different favorites. And, Yes, I like the BaseElements plugin a lot that Mike referenced. But I find myself more often using Monkey Bread Software because it supports so many more functions (3000+). Then again, MBS isn't free like BaseElements.
One word of caution about SQL on FileMaker. If you use the default FM methods of navigating layouts and inserting/updating/deleting records, then all of the fields are tied to their field ID number. This means that if you change field names, they change automatically throughout the whole solution.... well.... except for SQL statement which are hard coded text. I love SQL, but using a lot of it will make you more reticent to change field names. So you loose some of the FM elegance going the SQL route.
Thank you Mike and Taylor for your answers.
Have you used 2empowerFM SQL Runner from dracoventions?
No, but the 2empower Developer Assistant is one of my favorites for searching scripts and the relationship graph. Being that their other plugins work well at unusual tasks, I can vouch for Dracoventions being a good plugin developer. I just don't have experience with their SQL Runner.
Thank you Taylor,
Meanwhile I was installing BaseElements plug in.
Final question (hopefully).
Base elements work inside a calculation.
How do I run a calculation in a script, which in fact, should do nothing but the INSERT command from sql in the right table?
I would apreciate your help
I am running the below calculation with no success:
"INSERT INTO Log (
VALUES('John', 'Campo','Valor Previo', 'Valor Final')"
I believe that you can INSERT directly from FileMaker, without any plug-ins. Or, maybe I'm missing something. Here's the link to the FM SQL resource (this is for FM13, but):
I've integrated FM with SQL back end tables extensively. I didn't use any plug-ins.
I believe the OP is talking about using insert to create tables, etc. in FM, not in a SQL back end. Per the document you reference, only SELECT is supported for executeSQL.
And INSERT, UPDATE or DELETE are supported by the Execute SQL (script step, rather than the ExecuteSQL function).
Oh. I see. My bad.
It looks like you want to add a record to a FM table from another, possibly unrelated, FM table.
If it were me, I'd consider this a call for "brut-force programming". Just script it once (yes, a pita) and let the machine execute it effortlessly, over and over. If you encapsulate the script well enough, it essentially becomes a plug-in, yes?
Consider using the "New Window" script step to isolate the current task from the original found set, if needed (e.g. using "Constrain Found Set").
I succeeded with your SQL.
You can use the function anywhere, but may be good to use with "Set Variable" script step to see the result in variable.
If you failed, don't you try to insert into another file? BE have database option in 4th parameter.
BE_FileMakerSQL ( "
INSERT INTO Log (
VALUES('John', 'Campo','Valor Previo', 'Valor Final')
" ; "" ; "" ; "AnotherFile'sFilename"
The another file need to be opened (of course).
Not so bad, you can with defining DSN, as Mike wrote in 1st reply.