Teradata Copy Table



Copying tables in Teradata

How to make a copy of a table in Teradata?

Teradata provides some easy to use commands and ways to make a copy of a table – both DDL (table structure) and DML (data).

Example on how to copy tables in Teradata

The aim of this example is to migrate two tables from a testing environment to production.
The environment details are as follows:

  • DWDEV – development database
  • DWPROD – production database
  • D_PRODUCTS – products dimension table which needs to be copied with the data.
  • F_ORDERS – orders fact table for which only structure will be copied.

    Copy table structure with data

    Use the following SQL to copy table with data in Teradata:

    CREATE TABLE DWPROD.D_PRODUCTS AS
    DWDEV.D_PRODUCTS WITH DATA;


    The same results can be achieved by issuing the following statements:
    CREATE TABLE DWPROD.D_PRODUCTS AS DWDEV.D_PRODUCTS
     WITH NO DATA;
     INSERT DWPROD.D_PRODUCTS SELECT * FROM DWDEV.D_PRODUCTS;
    Note that the CREATE TABLE AS statement will not work if the source table has referential integrity constraints or any columns are defined as identity columns.

    Copy table structure

    Run the following SQL in Teradata to copy table structure only without data
    CREATE TABLE DWPROD.D_PRODUCTS AS
    DWDEV.D_PRODUCTS WITH NO DATA;
    Or
    CREATE TABLE DWPROD.D_PRODUCTS AS
    (
            SELECT * FROM DWDEV.D_PRODUCTS
    )
    WITH NO DATA;