Copying Tables

Another useful way to create a new table in a database is to simply copy the structure of an existing table. The way you do this varies from relational database to relational database.

In Oracle, the following statement would create a new table called My_Friends, which would be an exact copy (with data) of the structure (columns and data types) of the Friend table:

CREATE TABLE My_Friends AS SELECT * FROM Friend;

Note, however, that this doesn't copy any constraint definitions (primary keys and so on). This is one of the reasons why it's often recommended that you create only your basic columns and data types using the CREATE TABLE statement and then add all other constraints using the ALTER TABLE command. You can then simply apply your ALTER TABLE scripts to any tables copied in this manner, rather than having to go back through and add all the constraints again manually. If you want to copy only the table structure, with no data, then simply specify a WHERE clause that can never evaluate to true:

CREATE TABLE My_Friends AS SELECT * FROM Friend
WHERE 1=0;

In DB2, the syntax is similar:

CREATE TABLE My_Friends AS
 (SELECT * FROM Friend) DEFINITION ONLY;

Here, you use the DEFINITION ONLY keywords to ensure that you copy only the table structure.

Again, in MySQL, the syntax is similar:

CREATE TABLE My_Friends SELECT * FROM Friend
WHERE 1=0;

You should note that, for some reason, MySQL converts VARCHAR data types to CHAR during this process.

Finally, in SQL Server, you do things somewhat differently:

SELECT * INTO My_Friends FROM Friend
WHERE 1=0;