1 Reply Latest reply on Oct 28, 2014 9:06 PM by itraining

    Importing records into a work table

    johnlee

      I need to clear, then import month-to-date data into a table every day. Is there an easier way to clear this table than a DELETE ALL (which effectively doubles the processing time as it must first delete the records before it can import them again) ?

        • 1. Re: Importing records into a work table
          itraining

          Hi John

           

           

          Suggestion #1: delete smaller found set of older/unwanted records

          TableA = the table to be cleared each day

          TableB = the month/data table containing the data to import into TableA

           

          (1) In tableA, Show All Records

          (2) Import the TableB records

          - check the Update Existing option.

          - check the "Add New Records" option (If the number of records in TableB > TableA, you want the additional data to create new records in tableA)

          (3)(a) If the number of records in TableB < TableA

          - Show Omitted Only to isolate the older records in TableA that were not updated

          - Delete Found Set to delete the unwanted TableA records

          (3)(b) If the number of records in TableB >= TableA

          - there are 0 records to isolate and obviously 0 records to delete

           

           


           

          Suggestion #2: import empty records from dummy table to clear data - zero records are deleted

          TableA = the table to be cleared each day

          TableB = the month/data table containing the data to import into TableA

          TableC = duplicate of TableA with empty records (the number of records is always TableC > TableA)


          (1) duplicate TableA and rename it TableC (dummy table) and turn off all auto-enter field options

          (2) create new EMPTY records in TableC (if TableB hits 100,000 records create at least 100,000 records)

          (3) in tableA, Show All Records

          (4) import the TableC records

          - check the Update Existing option

          - only import if the record count in TableC > TableA (you need to ensure every record in tableA is cleared)

          (5) import the TableB records

          - check the Update Existing option.

          - check the "Add New Records" option (If the number of records in TableB > TableA, you want the additional data to create new records in tableA)

          (6) via Security setting or scripted Find and/or Custom Menu, hijack the Show All Records and Find commands in TableA so perform find only returns records with data

          - if TableA > TableB there will be empty records that the user does not see

          - there is no need to delete the unwanted records in TableA

           

           

           

          Hope this helps.

           

           

          Michael Richards

          Brisbane (Australia)