INSERT from Variable Array

[sal88]

Hi

I have a variable that contains multiple lines, each with fields separated by commas.

Can I call up this variable in the SQL INSERT function in order to create new records (based on the variable)?

Thanks

[jbante]

SQL INSERT statements are only available in FileMaker via a plugin, such as the Base Elements plugin.

Doing this in FileMaker without a plugin requires non-SQL techniques. Creating records through relationships in a file organized using the Selector-Connector relationship graph model is one possible approach (among many) with particularly attractive benefits.

[sal88]

I am using the 360Works ScriptMaster plugin which comes with an executeSQL function

[sal88]

Is this possible?

[Wim Decorte]

If you are asking if it is possible to use the plugin to create records through SQL INSERT commands then: yes.

Is it possible to use your variable as the "source" for those SQL commands: very likely.  In the end you need to create valid SQL syntax so it is really just a text parsing exercise.

[sal88]

So I have my $data variable which contains:

smith, john
scott, ridley

I want to create new records with this data, one record per line

So my SQL statement is:

"INSERT INTO people (surname, forename) 

SELECT " & $data

I've had no luck with this though, where am I going wrong?

[Wim Decorte]

you probably want to loop through your $data row by row and do an insert for each line so that you can capture and handle errors.

[sal88]

Even if I set my variable to a single line, I can't get the sql command to work though

[Josh Ormond]

The form doesn't match what the SQL statement is looking for. You are basically passing this:

"INSERT INTO people (surname, forename) 

SELECT smith, john ¶ scott, ridley"

I'm not sure it will work directly from the variable. But I could be wrong. You may need an intermediate Virtual List table so that you can specify the VALUES, which is missing from the statement, or at least the columns for the SELECT statement.

[sal88]

Ah OK, I had my doubts. I'm just going to export it to a csv (with SM's writetofile) and then import it. (The variable contents is from the geturlcontents function). That's super quick.

 

Thanks guys

[DanShockley]

I agree that inserting one row at a time is a good idea, but you also have a syntax problem. The SQL INSERT syntax is this: 

INSERT INTO table_name
VALUES (value1,value2,value3,...)

Or, if also specifying the columns to insert into, as you want: 

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...)

Your example had the command "SELECT" instead of the command "VALUES" - that might have been the problem. 

Of course, as you noted, exporting and importing can be pretty fast. There are downsides to that process (stability, control over specific errors, etc.), but it is one of the fastest methods when you need to create a lot of FileMaker records. 

 

 

[Josh Ormond]

There is a SQL function that allows for the use of the Select function:

INSERT INTO table1 (fieldName1, fieldName1)
SELECT fieldName3, fieldName4 FROM table2;

 

[sal88]

Very helpful guys. I wasn't too familiar with virtual lists either but can see them being very useful to me

Cheers

[sal88]

BTW can you do an UPDATE query with SM's SQL?

[sal88]

BTW can you do an UPDATE query with SM's SQL?

Turns out you can