Zeroing or deleting table data

You can zero or delete the data in a table by using the Zero/Delete Table Data utility. For example, you might use this utility to clear data before starting a new cycle of planning, or to clear testing data.

When you zero data in a table, the existing rows are retained, but the non-key column values are changed as follows:

  • Numeric columns and Integer columns (all types) are changed to zero.
  • String columns are changed to blank values.
  • Date, DateTime, and Boolean columns are changed to their default values.

When you delete data in a table, the entire row is deleted from the table.

You can choose to zero or delete all rows in the table, or you can specify a filter to zero or delete a subset of rows. Additionally, when zeroing data, you can zero all columns or only selected columns.

Copy Table Data is only available to administrators or to users with the Administer Tables permission. For non-admin users, the user's write access permissions to the table apply.

To zero or delete data from a table:

  1. On the Axiom tab, in the Administration group, select Tables > Table Administration > Zero/Delete Table Data.

    The Zero/Delete Table Data dialog opens.

  2. For Source table, specify the table where you want to zero or delete data. To select a table, click the Choose table icon or double-click in the box to open the Select Table dialog.

    Only data tables and reference tables are listed. Document reference tables are controlled by their source documents and therefore are not available to be edited via this utility.

    NOTE: If a table is set to read only, then it is not listed here. You cannot zero data or delete rows in a table that has been set to read only.

  3. In the Filter box, specify the set of data that you want to zero or delete:

    • If you want to zero or delete all rows in the table, then leave the Filter box blank.

    • If you want to zero or delete a subset of rows, then define a filter in the Filter box. For example, the filter Dept=200 would zero or delete only the data for department 200.

      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 zeroed or deleted, and any lookup tables for that table.

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

  4. In the Processing Action section, specify whether to zero or delete data:

    • Zero selected columns in rows: This action zeroes rows in the table, limited by the filter (if specified). If this option is selected, then you must also specify which columns in the table to zero.
    • Delete rows: This action deletes rows in the table, limited by the filter (if specified).

    NOTE: If the source table uses the Large Table index scheme, then it is not supported to zero data in that table. The only available option is to delete rows.

  5. If you selected to zero data, then specify which columns in the table to zero:
    • To zero all columns, select the check box in the header to select all columns.
    • To zero only selected columns, select the check boxes for the desired columns.

    Key columns, alternate key columns, and validated columns are never zeroed and are not available to be selected.

    NOTE: If a column in the table is an identity column or has been set to read-only, then that column cannot be zeroed and is not available to be selected. The text "(R/O)" displays next to the column name.

  6. Click Zero or Delete (depending on which action you are performing).

  7. Axiom Software calculates how many rows in the table will be affected, and then displays a message listing the number of rows and asking if you want to proceed. Click Yes to continue.

    If the number of rows is not as expected, you can click No to return to the dialog and change your settings.

    NOTE: If you are deleting all of the rows in a table, the table will be truncated, which is a faster deletion process. If you are deleting a subset of rows in the table, and the number of rows to delete exceeds 50,000, then you will be warned that the deletion process may take some time.

The specified data is deleted or zeroed.

TIP: You can also delete or zero data by right-clicking the table in Axiom Explorer (or the Explorer task pane) and then choosing Zero/Delete Table Data. The table will automatically be set as the source table.