KB1022

Copy Table Data errors when copying large amounts of data

Summary

By default, the Copy Table Data utility is limited to copying no more than 250,000 rows of data. If you need to copy larger amounts of data, create an import utility to import data from the source table to the destination table, and then run the import using Scheduler.

Details

When you use the Copy Table Data utility to copy more than 250,000 rows of data, the following error message occurs:

Example error when copying more than 250,000 rows of data

Axiom Software enforces a maximum row limit because copying large amounts of data can consume a lot of resources and impact the overall performance of the system for end users. In order to work around this limitation and avoid impacting system performance, you can copy the data as follows:

• Create an import utility to import data from the source table to the destination table
• Define a SQL SELECT statement in the import to query the source database
• Use Scheduler to execute the import utility outside of peak business hours

Resolution

To copy over 250,000 rows of data, create an import utility, and a Scheduler job to execute the utility.

To create an Import utility:

Only administrators can create or edit an import utility that copies data within Axiom Software, using the Current Axiom system database source. However, once the import is created, non-administrators can be given the right to execute it.

1. On the Axiom tab, in the Administration group, click Imports > Create New Import.

   NOTE: In systems with installed products, this feature may be located on the Admin tab.

2. In the Create New Import dialog, select Create from scratch (default).

3. On the General tab, define the import name.

4. On the Source tab:

   • Set the Import Type to Internal Database.

   • Set the Import Source to Current Axiom system database.

   • Click the SQL Editor button and write a SQL query that will pull all records from the source table. For example:

     SELECT * FROM {GLDetail2019}

     This example retrieves all the data from the GLDetail2019 table. The curly brackets {} indicate the system variable for the table name.

     SELECT * FROM {GLDetaill2019} WHERE ACCT = 7015

     This example filters the data to a single account.

   

5. On the Mapping tab:

   • Set the Destination table to the table the data is being copied to.

   • Use the Auto-generate temp table and destination columns button to automatically generate the column mappings. Audit columns and calculated fields will display as <not mapped>. Leave them as is and do not attempt to manually map them.

   

   Click the OK button and save the import.

6. In the Save As dialog, navigate to the folder where you want to save the import, then click Save. By default, the import will be saved to the root of the Imports Library. You can create a new sub-folder from this dialog if desired (and if you have the appropriate permissions).

To create a Scheduler job to run the import:

Only system administrators and users with the Scheduled Jobs User security permission can access Scheduler.

1. On the Axiom tab, in the Administration group, click Manage > Scheduler.

   NOTE: In systems with installed products, this feature may be located on the Admin tab. In the System Management group, click Scheduler.

2. In the Scheduler dialog, click on the Job tab at the top section and then click New. A new job is opened in the dialog, with a tab name of New Job.

3. Click Add > Import ETL Package to add the task to the new job. The task is added to the job, and you can now configure the task properties.

4. In the Task Details, select the import you created from the Select ETL Import Package drop-down menu.

   

5. Click Save. You can define a name for the job and save it to the desired location in the Scheduler Jobs Library.

6. Either configure the Scheduling Rules if you want to run this at a specific time, or click Run Once to run the import job right away. For more information, see Defining scheduling rules for a job or Running a job.

Outcome

The job will be executed by Scheduler and send you a notification when it has completed importing the data. If the notification indicates a failure, please contact Kaufman Hall Software Support for assistance.

See also

• Importing data
• Importing from an internal Axiom Software database
• Scheduler
• Scheduler task: Import ETL Package

Article information

Category	Imports
Applies To	All Versions
Tags	Copy Table, Maximum of 250000 Rows
Issue Number(s)	N/A