Copy Table | Data Mover Portlet | Teradata Data Mover - Copying a Table - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Published
June 2021
Language
English (United States)
Last Update
2021-06-16
dita:mapPath
fcu1619423186706.ditamap
dita:ditavalPath
mpm1591127278842.ditaval
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem
You can copy table data, statistics, or both to the same or a different location on a target system. You can rename tables, validate row count, perform partial table copy, and designate additional table copy options.

Tables can be copied between systems supported by Data Mover.

  1. In the object browser, click the check box next to the name of the table you want to copy.
  2. Click "" to display the Table Settings dialog box.
    The name of the table displays at the top of the dialog box.
  3. [Optional] For Teradata tables, select one of the following criteria under Copy the following to restrict the copy job.
    • Only data
    • Only statistics
    • Both data and statistics
  4. [Optional] Enter the predicate of the WHERE restrictive clause in the Include Only Where box to perform a partial table copy.
    Do not enter the keyword WHERE. For example, if you want to restrict a partial table copy to only the columns in which col1 is greater than 4, type col1 > 4 in the box.
  5. [Optional] Enter the name in the Rename to box to use an alternate name for the table on the target database.
  6. [Optional] To map to a different database or designate staging tables for tables and temporary artifacts, click "" next to the associated field, and select a database.
    You can use the filter to display only the items that match the characters you enter.
  7. [Optional] Select the Use source staging table check box to enable source staging.
  8. [Optional] If Use source staging table is enabled, select a source database from Source staging database for tables.
    If a database is not specified, Data Mover uses the same database as the table being copied. Data Mover automatically names the staging table.
  9. [Optional] To use a separate staging database for the target, click "" to the right of Target staging database for tables and select a database.
  10. [Optional] Select the Use target staging table when table exists on target check box to force a target staging table to be created.
  11. [Optional] To use a separate staging database for temporary artifacts, click "" next to Target staging database for temporary artifacts and select a database.
  12. [Optional] Select one of the following options from the Validate Row Count list to verify that the number of rows in the source and target tables match after completing the copy job.
    Option Description
    None Do not check row count. None is the default setting.
    Partial Perform a row-count check on the subset of data that was copied. Only applies when doing partial table copies.
    All Perform a row-count check on the entire table. Applies to full or partial table copies.
  13. [Optional] Select one of the following options from the Compare DDL list to verify that important aspects of the DDL for the source and target tables match before performing the copy.
    Option Description
    Default By default, Data Mover compares the DDL.
    True Compare the DDL before performing the copy.
    False Do not compare the DDL before performing the copy.
  14. [Optional] Select one of the following options from Database to Client Encryption to enable encryption at the job, table, or view level.
    Option Description
    Default Use the encryption setting specified in the daemon settings.
    True Override daemon settings and enable encryption.
    False Override daemon settings and disable encryption.
  15. [Optional] Select one or more key columns from the Key Columns list to specify which columns uniquely identify each row when upserting them into the target table in a partial table copy.
    You must specify at least one key column from the list. You can select multiple columns by holding the Ctrl key and clicking the column names in the list. Specify all primary indexes as key columns if at least one of the primary indexes is unique.
  16. [Optional] Click Advanced.
  17. [Optional] Select one of the following options from the Staging to Target list to designate how to copy data from a staging table to a target table when a target table already exists.
    Option Description
    Default Data Mover chooses the most efficient way to copy data from the staging table to the target table.
    Delete insert Rows from the target table are deleted with the DELETE statement, then copied from the staging table to the target table with the INSERT/SELECT statement. The rows to be deleted depend on the job. With a partial table copy, only the rows that match the SQL query are deleted. With a full table copy, all rows are deleted.
    Merge Uses the MERGE statement to copy data. An error results if the MERGE statement cannot be used. The MERGE statement cannot be used to copy multiset tables.
    Insert only No rows are deleted from the target table. Instead, the INSERT/SELECT statement copies rows from the staging table to the target table.
  18. [Optional] For Teradata to Teradata jobs only, select one of the following options from the Export Without Spool list to control the spooling that precedes a table copy when using the Teradata PT API utility.
    You can export a table without spooling only in Teradata Database 13.10 or later.
    Option Description
    Default By default, the Teradata Database does not write to spool space before copying tables.
    True Never write to spool space before copying the table.
    False Always write to spool space before copying the table.
  19. [Optional] For Teradata to Teradata jobs only, select the Override lock access check box to allow updates to the source table when copying to the target.
    Use this option with extreme care. If you select this option, you risk copying data to the target table that has not been committed on the source table. This option only applies to copy jobs that use Teradata PT API and Teradata JDBC.
  20. [Optional] To select a map when copying at the object level, click "" to the right of Target System Hash Map to view a list of available maps.
    When Default is selected, the system determines the best map for the job.
  21. Select OK.