Using Teradata SQL: Create a table from another table or select statement

Jamie Whitehorn -

OK, this one is fairly basic for anyone who regularly uses SQL, but it's a neat trick if you're not familiar with it.

Normally when you create a table you have to specify all the fields and their formats in the CREATE statement. Then you populate it with an INSERT statement

But there is an alternative ...

The 'CREATE TABLE ... AS' statement

This little marvel can be used to:

  • Create a full copy of an existing table.
  • Create a new copy of a table that contains only some of the original records - a subset.
  • Create an empty table but with exactly the same structure of the original.
  • Create an amalgamation of several tables.

It allows you to very quickly create a table from the values in another table without having to bother using a CREATE TABLE statement or specifying the data types of the fields.

The example below would create a new table called active_employees based on entries in the employee table that have the active_flg set to 'Y'. Note that we don't have to specify data types for the fields we picked. SQL will pick those up automatically from the definitions of the existing employee table.

CREATE TABLE active_employees AS (
   SELECT
      *
   FROM employee e
   WHERE
      e.active_flg = 'Y'
   )
WITH DATA;

Create a full copy of an existing table

The more observant amongst you will also have realised that it lets you copy a complete table just as easily.

CREATE TABLE test_employee_table AS (
   SELECT
      *
   FROM employee e
   )
WITH DATA;

Create a new copy of a table that contains only some of the original records - a subset

Including a WHERE statement in the populating SELECT statement lets you limit the records in the new table.

CREATE TABLE 2105_customers AS (
   SELECT
      customer_number,
      customer_name
   FROM customers
   WHERE
      first_order_date = '2015'
   )
WITH DATA;

Create an empty table but with exactly the same structure of the original

By leaving out the words WITH DATA from the end of the statement you can create a new table that has exactly the same structure as the original but without any data in it.

CREATE TABLE new_customers AS (
   SELECT
      *
   FROM customers
   ) 
WITH NO DATA;

Create an amalgamation of several tables

If you want to get info from several linked tables at the same time, just use a JOIN ...

CREATE TABLE order_details AS (
   SELECT
      *
   FROM customers c
      JOIN orders o ON c.cumtomer_number = o.customer_number
   );

Using the wildcard * here, will copy all the fields from customer table followed by all the fields from the orders table. Another useful little trick, especially if your trying to debug the data :)

Creating a temporary or volatile table

You can also use these techniques with volatile tables. Volatile tables are ones that only exist for the length of your session. They're very useful, as they're automatically deleted when you log out, which helps reduce clutter and keep things tidy. They're also useful if you're going to be creating jobs that other people will run as they'll automatically have the correct permissions on any table they create.

CREATE VOLATILE TABLE new_customers AS (
   SELECT
      *
   FROM customers
   ) 
WITH DATA ON COMMIT PRESERVE ROWS;;

The same trick can be used to create an empty volatile table using another table as a template for the structure by using the "WITH NO DATA" suffix at the end

CREATE VOLATILE TABLE new_customers AS (
   SELECT
      *
   FROM customers
   )
WITH NO DATA ON COMMIT PRESERVE ROWS;;

Gotchas

There is a downside to this convenience - a CREATE AS statement can run slower that a CREATE / INSERT sequence ... much slower. In one of my recent SQL constructs a CREATE AS took over 15 minutes to run. The same statement, seperated as a CREATE followed by an INSERT ran in 21 seconds.

CREATE TABLE new_customers AS (
   SELECT
      *
   FROM customers
   ) 
WITH NO DATA;

INSERT INTO new_customers
  SELECT
    *
  FROM customers
  WHERE last_activity_dt >= ADD_MONTH(CURRENT_DATE, -1)
;

Summary

Basically anything that you can express as a SELECT statement can be used in the CREATE TABLE AS statement to make a new table. A simple trick, but very useful once you get the hang of it.

"Speak when your angry and you'll make the best speech you'll ever regret" — Lawrence J. Peter