9 Replies Latest reply on Jun 16, 2017 6:22 AM by philmodjunk

    Copy data one table to another


      Hi there


      I have a routine which requires data to be copied from a temporary scratch table (used for data validation, end user cleansing and verification etc) to the main data table.


      Currently this is handled by a pretty simple loop in a script which goes something like this:


      On scratch table

      Go to first record


           Set variable 1

           Set variable 2


           Set variable 6


           Go to destination data table


           Create new row

           Set field [1 ; $variable1]

           Set field [2 ; $variable 2]


           Set field [6 ; $variable 6]


           Go to layout [Scratch temp layout]

           Go to next record ; exit after last


      end loop


      Put simply, this takes forever for only 78 rows to go into the table.  Around 3-4 minutes in a multi user environment user FMS 13.  At this time, I am the only user on the network.


      There must be a better way of getting $variables 1-6 in to new fields on new records in the destination table.  I have considered parsing data from a virtual list, but I already have 130000 rows of existing data in the destination table which could be an issue to a fundamental change like this.


      Any ideas gratefully appreciated.

        • 1. Re: Copy data one table to another

          Copy the primary key, go to the other table, set a foreign key and set the fields via a utility relationship.


          Or use the Magic Key technique to create one new related record per iteration and "push through" the values. (Basically the same method as above, just "from the other side".)


          Or simply import the found set from the scratch table; that should also be the fastest solution, all else being equal.

          1 of 1 people found this helpful
          • 2. Re: Copy data one table to another

            Export the records to a scratch file and then import the record to the table where you need them. This is pretty fast.


            If you want to speed up the script process you can do that as well. Is this process of copying the only way new records get into the destination table?


            Setting up the fields with auto enter calcs for the variables saves all the set field steps and requires only a new record be created. It's a little faster. You can also pull all the data in with eSQL and parse that faster that jumping layouts/tables  back and forth in the script.

            • 3. Re: Copy data one table to another

              Looks like importing from a table would suit, given the data structure I already have.


              In the script however, the import records command only has options to import from a file, as bigtom mentions.


              Is there a way to 'import' from one table to another?


              My other concern would be what happens to the exported scratch file if I chose that option as there is a large volume of imports happening daily from users.

              • 4. Re: Copy data one table to another

                In this case the export files can be local and unique to each user. That solve the multi user issue.


                When you are are done with the import just trash the file. create a new one on the next export.


                Do you have the the ability to run the process via PSOS. No need to wait at all then.

                • 5. Re: Copy data one table to another



                  I have thought about PSOS yes, so this gives an option as well.  Something I will look at.

                  Couple of ideas here, thanks for the help

                  • 6. Re: Copy data one table to another

                    Define an External Data Source. That can be (and in your case, is) the very same file you're working in.


                    Now after you've selected that new data source, you can choose a scratch table TO as the import source.

                    • 7. Re: Copy data one table to another

                      There is no need to export just to be able to. Import. import records can move records from one table to another in the same file. When asked to specify the file from which to import, select the file you already have open.

                      • 8. Re: Copy data one table to another

                        Ah, making a little sense now, although not 100%, apologies.


                        Something like, very simply:


                        Set Variable [$File ; get(FileName)

                        Import Records ["$File" ; etc]


                        Would set the filename initially


                        I think I have that although will defer if that's wrong. 


                        The issue I'm having next is the actual import mapping dialog screen.  I can obviously set the destination table and fields to the right side of the dialog, but the left side representing the source has no options.  I'd would have imagined I'd need to do something here so as to get (sic):


                        Source field 1 > Destination field 1

                        Source field 2 > Destination field 2



                        simply because FM at this point would not know which source fields to map to which destination fields?

                        • 9. Re: Copy data one table to another

                          The script step can't use $path to map the fields. Since the script isn't running yet $path has no value. Click Add File and use it to select the file. After you finish mapping fields, you can go back and put in the $path variable.