DWHPro Insights

Copy a Table Between Teradata Databases: CREATE TABLE AS

By Roland Wenzlofsky ·

Copy a Table Between Teradata Databases: CREATE TABLE AS

To copy a table from one database to another on the same Teradata system, use CREATE TABLE target_db.table_name AS source_db.table_name WITH DATA;. This creates a new table and copies its rows; it does not remove or redirect the source table. Use WITH NO DATA when you need an empty target.

This guide covers direct table copies, query-based copies, statistics, primary indexes and checks before switching applications to a new table. The examples use illustrative database and table names; substitute your own names and review the target definition before running them.

Verified example: WITH DATA versus WITH NO DATA

Lab verification: 22 October 2026. The example below was executed on Teradata Database 20.00.28.81 using BTEQ 20.00.00.11 in Teradata (BTET) transaction mode. Each SQL statement was submitted as a separate request. These are small functional checks, not performance benchmarks; results involving transaction boundaries can differ in ANSI mode or an explicit transaction.

CREATE MULTISET VOLATILE TABLE dwhpro_ai_source
(id INTEGER, label VARCHAR(12))
PRIMARY INDEX (id) ON COMMIT PRESERVE ROWS;

INSERT INTO dwhpro_ai_source VALUES (1, 'alpha');
INSERT INTO dwhpro_ai_source VALUES (1, 'alpha');
INSERT INTO dwhpro_ai_source VALUES (1, 'beta');

CREATE MULTISET VOLATILE TABLE dwhpro_ai_copy
AS dwhpro_ai_source WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE dwhpro_ai_empty
AS dwhpro_ai_source WITH NO DATA ON COMMIT PRESERVE ROWS;

SELECT COUNT(*) AS copied_rows FROM dwhpro_ai_copy;
SELECT COUNT(*) AS empty_rows FROM dwhpro_ai_empty;
SHOW TABLE dwhpro_ai_copy;

DROP TABLE dwhpro_ai_empty;
DROP TABLE dwhpro_ai_copy;
DROP TABLE dwhpro_ai_source;

Observed results: WITH DATA produced 3 rows; WITH NO DATA produced 0. SHOW TABLE confirmed a MULTISET volatile target with columns id and label, PRIMARY INDEX (id), and ON COMMIT PRESERVE ROWS. The duplicate source row was retained.

This isolated check uses volatile tables to demonstrate the two data-copy options. It does not test cross-database privileges, statistics copying, dependent objects or large-table performance. The permanent-table templates above require those additional checks. Review SET and MULTISET semantics and available spool space before running a production copy.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Copy a table between databases on the same system

CREATE TABLE target_db.customer_copy
AS source_db.customer
WITH DATA;

The target database must already exist, the target table name must be available, and your user needs the required source access and target creation privileges. Ensure the destination has sufficient permanent space and the operation has sufficient spool capacity. The source remains available at its original name.

This is a data-copy operation, not a storage-free clone. Plan its resource use and locking implications for large or actively changing tables. It also does not transfer a table to a different Teradata system: that requires an approved transfer process, such as export/load or a suitable migration utility.

Create an empty copy with WITH NO DATA

CREATE TABLE target_db.customer_empty
AS source_db.customer
WITH NO DATA;

Use the direct table form when the source definition is your starting point. Inspect it with SHOW TABLE and adjust the design deliberately if the target workload differs. If you later load the table using INSERT ... SELECT, use explicit column lists so schema changes do not silently alter the mapping.

Copy selected columns or rows with a query

CREATE MULTISET TABLE target_db.active_customer AS
(
    SELECT customer_id, customer_name
    FROM source_db.customer
    WHERE status_code = 'A'
) WITH DATA
PRIMARY INDEX (customer_id);

This example assumes the source has customer_id, customer_name and status_code columns, and that customer_id is a suitable distribution key for this workload. It explicitly chooses MULTISET semantics and a nonunique primary index. Those choices are examples to assess, not defaults to copy into every design.

Notice that the SELECT is enclosed in parentheses and the index clause follows WITH DATA. A query-based table creation is not equivalent to a direct table copy: table options and column attributes follow different inheritance and defaulting rules.

What is copied, and what needs checking?

  • Table and column definitions: compare SHOW TABLE output for source and target. Direct table copies preserve more of the source definition than query-based copies, but “everything is copied” is too broad. Review defaults, nullability, character sets, case sensitivity, compression, partitioning and constraints for your release and syntax.
  • Distribution and duplicate handling: verify the primary index or NoPI choice, SET/MULTISET behaviour and any uniqueness constraints. Do not rely on an assumed first-column primary-index default; defaults also depend on table attributes and system settings.
  • Dependent objects and access: review grants, views, macros, procedures, load jobs and application references separately. Creating a table does not repoint consumers or complete a database migration.
  • Statistics: decide whether eligible source statistics should be copied, then inspect warnings and check their suitability for the new data and layout.

Copy statistics with AND STATISTICS

CREATE TABLE target_db.customer_with_stats
AS source_db.customer
WITH DATA AND STATISTICS;

Teradata can copy eligible statistics with this form. Eligibility depends on the source and target definitions and whether a query changes the result. Read any warnings: statistics may be copied, zeroed or omitted under the documented rules. A filtered or transformed copy may need fresh statistics that describe its own rows.

Do not assume that copying statistics removes the need for future collection. Data changes and different access patterns can make the copied statistics unsuitable. For an empty target, review the documented WITH NO DATA rules rather than treating its statistics as a populated-table baseline.

Validate the copy before using it

SHOW TABLE source_db.customer;
SHOW TABLE target_db.customer_copy;

SELECT COUNT(*) AS source_rows
FROM source_db.customer;

SELECT COUNT(*) AS target_rows
FROM target_db.customer_copy;

These checks apply to the full, direct copy in the first example. Equal row counts are only an initial check. Compare business totals, key uniqueness, null patterns and representative row values as required. For a filtered copy, compare against the same source predicate. Counts taken while the source is changing may legitimately differ; agree a consistent validation window or snapshot approach.

  • Verify the target DDL, access privileges and dependencies.
  • Reconcile business results and investigate differences rather than relying on row counts alone.
  • Check statistics, distribution and representative query performance.
  • Plan application changes, scheduling and rollback before switching consumers.
  • Retain the source until validation and the agreed cutover process are complete.

Common questions

Does CREATE TABLE AS move or rename the source? No. It creates another table. The source is unchanged by the copy statement; moving consumers and retiring an old table are separate operations.

Can I copy into an existing target? CREATE TABLE requires a new target table. To add rows to an existing table, use a separately reviewed INSERT ... SELECT with explicit column mappings and duplicate-handling rules.

Can I use this between different Teradata systems? Not as a simple cross-database CREATE TABLE AS statement. Choose a transfer method suitable for your systems, data volume, permissions and consistency requirements.

References and next steps

The examples follow Teradata’s CREATE TABLE AS primary-index examples and statistics-copy rules. Check the documentation for the release you operate.

Reproduce the lab checks

Download the SQL used for these functional checks. Run it in a separate BTET lab session with unused dwhpro_ai_* table names. It creates and drops only its own volatile tables and reads DBC metadata; it contains no login credentials. Execute the statements separately, as in the test. The file covers date formatting, duplicate rows, commit behaviour, table copying and spool counters.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “Copy a Table Between Teradata Databases: CREATE TABLE AS”

  1. Hello,

    Regarding creating tables from sql statements, is there any downside from locks on system tables (DBC)? Looking at the explain plans I understand that some dbc tables are locked on table level for the whole duration of executing the select statement. Is it like this, and if yes, wouldn’t that prevent other users from creating/modifying tables etc?

    Regards
    Ilkka

    Reply
  2. Hello,

    Nice post, really detailed. I totally agree to use for Production Env, the time-wasting approach.Extract DDL, insert data, collect Stats.

    As you said, manually define the datatype/Nullable or not, this thing, a lot of people they forget it, and TD take as default the first returning row of the query. So, in more complex queries where I make a new table or volatile one, some columns might not be nullable or in a different datatype and when I will use these tables for some other work, I will have an impact on performance.

    Ending, just to add one more parameter, that I learned in a bad way, is what if I have Foreign Keys and Triggers defined on my table and I want o make a clone one? Can I use the TD syntax?

    Reply
  3. Hi Roland

    This is a very good post on Table Cloning.
    The two images in the post very clearly explain the difference between the two approaches.
    One thing which I find missing is about Partition Primary Index.
    In the SQL Statement approach, PPI of Source Table is also not transferred.

    Regards
    Nitin

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam