Jump to content

INSERT from Variable Array


This topic is 3054 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

 

 

Link to comment
Share on other sites

This topic is 3054 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.