Import records between two tables in the same database
I imagine there is a suggested best practice for what I'm trying to accomplish, and the new Perform Script on Server script step may help.
I have two tables within the same database running on FM Server 13. Let's call them Source and Work. I need to copy some of the records from Source into Work so that I can do some special manipulations on the records in Work. Since they're both in the same database, a straight import from Source into Work isn't allowed. (If it were, this would be a non-issue.)
The workaround I've come up with is to export the records from Source into an external .csv file, then turn around an import that same .csv file into Work, all automated with a script, of course.
Question 1: Is this the best practice? Is there an easier way to get data copied between two tables in the same database?
If there isn't a better workaround, I have a second issue related to location and permissions when doing this on FM Server. If I'm running FileMaker Pro on a client machine, the place where the .csv file is stored must be accessible to the Client machine. (In other words, the .csv file can't easily be stored in a temporary location on the server.) But if one of my customers is using WebDirect, they obviously won't have access to the same .csv file location the Client machine is using.
Question 2: Is there a way to allow both WebDirect and FM Pro client machines to both use the same temporary storage location for the .csv file?
I could probably make a location on the server that would be available to Client machines running on the same LAN as the server, but that wouldn't work if the Client machine were not directly on the LAN. One thought is that I could make the location where the .csv file is stored be dynamic, based on the kind of user, but that isn't very elegant and will probably still run into problems in some situations.
Question 3: Would Perform Script on Server help in this case?
I can see that always performing the two steps (export .csv file, then import the same file) could be helped a great deal if those two scripts were always performed by the server (it would resolve the location and permissions issue). However, if my understanding of the Perform Script on Server is correct, the script being run on the server has no knowledge of the current found set in the Source file, so the records exported to the .csv file wouldn't necessarily be the set of records I'm looking to work on in the Word file. Perhaps I'm missing something.
Thanks for any advice you have.