export to SQL

[Rothko]

I am working on a way to automatically export all records from Filemaker to an SQL server.

I've got this working first via ESS (Via a looping script: go to local layout, set variable, go to SQL layout, make new record, set field, ...). This takes about a second per record.

Then I've tried it via the 'execute SQL' script step ("INSERT INTO " ...). This takes even longer.

I'm wondering if I can use Multirow-Inserts (see http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts ) to insert all records at once. Maybe not going back and forth between tables and layouts can speed things up?

What about getting the entire local FM database into a variable, in concatenated form (e.g. by a loop, or by 'copy all records') Or a variable for each field, with concatenated values. That should be possible too. If it's a good idea, which I'm not sure of. (?)

But then how do I parse that to get it into the 'execute SQL' query?

Thanks for your comments...

[mr_vodka]

If this is not a common reoccurring thing, then why dont you use the FileMaker ODBC driver? You can put a copy of the FileMaker files on a box and then use ODBC to import into SQL server.

[Rothko]

Well, i do want it to be re-occurring, as part of an automatic synch routine between the two databases.

I've now managed to do it. I've created a special layout on which the 'copy all records' script step is run. This layout includes fields that have things like

INSERT INTO sql_table (first, last, email, newsletter) VALUES ('

 

and

', '

and

');

auto-entered in them.

The result of the 'copy all records' script step is then stripped of all its tabs, to create a perfectly formed SQL query.

This results in one long enormous SQL query, which is of course longer than acceptable. I therefore needed to run a loop on the SQL query field, taking ten lines out at a time and giving only this shorter query to the 'execute SQL' script step. The loop ends when the 'GetValue' function comes up as empty, which means that we're at the end of the text to be parsed.

This runs at about 15 records per second. Still not perfect but much, much better than 1 per sec!