How to Copy a Table: Teradata vs Oracle syntax

In Oracle, you copy a table with the following CREATE TABLE statement:

CREATE TABLE <table_copy> AS 
SELECT * FROM <table>

And if you only want to copy the structure (with no content) you add WHERE clause that filters out all rows:

CREATE TABLE <table_copy> AS 
SELECT * FROM <table>
WHERE 1=2

In Teradata, you have two ways to do the same task:

CREATE TABLE <table_copy> AS (
SELECT * FROM <table>
) WITH [NO] DATA

and a shorter way:

CREATE TABLE <table_copy> AS <table> WITH [NO] DATA

In both cases, WITH DATA (or WITH NO DATA) clause is required.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s