Copying Table structure with DATA 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).

EX : HOW TO COPY TABLES 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.

1) 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;

2) The same results can be achieved by issuing the following statements:

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

3) 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.

4) 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;