AX3640

Copying table data

You can copy data from one table to another, or within the same table. If you are copying between tables, the source and destination tables must share common key columns.

The destination table must already contain columns to hold the copied data. Copy Table Data does not create columns as part of the copy operation.

You can copy all rows of data, or you can specify an optional filter to limit the data to be copied.

NOTE: By default, the Copy Table Data utility is limited to copying no more than 250,000 rows of data (via the MaximumRowsAllowedThroughCopyTableData system configuration setting). If you need to copy more rows than that, we recommend creating an import utility (to import from the source table to the target table) and then scheduling that import to run via Scheduler.

To copy table data:

  1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Copy Table Data.

    The Copy Table Data dialog opens. The top section of the dialog specifies the source and destination tables for the copy operation. The middle section can be used to define an optional filter. The bottom section specifies which columns are to be copied from the source table, and which columns in the destination table will receive the data.

  2. For Source Table, specify the source table from which to copy data. To select a table, click the Choose table icon or double-click in the box to open the Select Table dialog.

    By default, the dialog is filtered to only show data and reference tables, but you can copy data from a document reference table if desired. Select the Doc. Reference check box to see these tables.

  3. For Destination Table, specify the target table for the copied data. To select a table, click the Choose table icon or double-click in the box to open the Select Table dialog.

    NOTE: Document reference tables and read-only tables cannot be specified as the destination table.

  4. If desired, in the Filter box, specify a filter to limit the data to be copied.

    For example, the filter Dept=200 would copy only the data for department 200. If no filter is defined, then all data rows are copied for the selected columns.

    You can type in the filter or use the Filter Wizard . The selections in the Filter Wizard are automatically limited to the relevant tables—the table being copied, and any lookup tables for that table.

    To check that the filter uses valid syntax, click Validate filter .

  5. In the Column mapping section, specify the source columns to be copied by selecting the check box to the left of the column name.

    NOTE: Calculated fields are listed in the source columns. If desired, you can copy from a calculated field to a "normal" column. This will take the current calculated values in the calculated field and copy them as static values to the destination column.

    You can select the check boxes for individual columns, or you can use one of the following:

    • Click Select physical columns to select only the literal columns in the table, excluding any calculated columns.
    • Click the check box in the column header to select all columns, including any calculated columns.

    All key columns must be included in the copy operation and are selected by default.

  6. In the Column mapping section, specify the destination column for each selected source column.

    You can manually select the destination columns by using the drop-down list for each column, or you can click Auto-generate destination column mapping to have Axiom Software "guess" the appropriate destination columns based on column names and data types.

    In most cases it will be easiest to auto-generate the mappings and then make any edits as necessary.

    You only need to specify a destination column for each source column to be copied. If a destination column is specified but the check box is not selected for the source column, then that entry will be ignored.

  7. Click Copy.

    The Copy button is not available until all required settings have been made and no invalid settings exist. If the settings contain errors, an error message displays at the bottom of the dialog.

The copy operation begins. Axiom Software displays a confirmation message when it is complete, or an error message if the operation experienced errors.

The Copy Table Data dialog remains open with the current settings until you close it. If the operation experiences errors, you can change the settings and try again.

TIP: You can also copy data by right-clicking a table in Axiom Explorer (or the Explorer task pane) and choosing either Copy data from this table or Copy data to this table. The table will automatically be set as either the source table or the destination table as appropriate.