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

    Copy data one table to another

    Stu412

      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

      loop

           Set variable 1

           Set variable 2

           etc

           Set variable 6

       

           Go to destination data table

       

           Create new row

           Set field [1 ; $variable1]

           Set field [2 ; $variable 2]

           etc

           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
          erolst

          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
            bigtom

            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
              Stu412

              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
                bigtom

                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
                  Stu412

                  Bigtom

                   

                  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
                    erolst

                    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
                      philmodjunk

                      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
                        Stu412

                        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

                        etc

                         

                        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
                          philmodjunk

                          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.