How to make a copy of a table in Teradata?
To 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.
