Skip to content
Home » How do you create a copy of an existing table in SQL?

How do you create a copy of an existing table in SQL?

Copying Only Data

You can create a copy of an existing table in SQL by using the CREATE TABLE AS statement. This statement creates a new table and fills it with data computed by a SELECT command. The data types of the new table columns are derived from the data types of the expressions in the SELECT list.

Here is the basic syntax:

CREATE TABLE new_table AS SELECT * FROM existing_table;
SQL

In this statement:

  • new_table is the name of the table that you want to create.
  • existing_table is the name of the existing table that you want to copy.

This command will copy all columns and data from the existing_table to the new_table.

Please note that this command does not copy the constraints like Primary key, Foreign key, etc. If you want to create a complete copy of a table including both data and constraints, you would need to first create the table with its constraints, and then insert data into it.

Copying Constraints and Data

If you want to create a complete copy of a table in SQL, including both the data and the constraints such as Primary Key, Foreign Key, etc., you would need to follow a two-step process:

Create the new table with its constraints

First, you need to create the new table with the same structure as the existing table, including the constraints. You can do this by using the CREATE TABLE statement with the same column definitions and constraints as the existing table. Here’s a simplified example:

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    column1 datatype CONSTRAINT,
    column2 datatype CONSTRAINT,
    ...
);
SQL

In this statement, replace new_table with the name of the new table you want to create, and replace id, column1, column2, etc. with the column names and constraints from the existing table. The datatype should be replaced with the data types of the respective columns.

Insert data into the new table

After creating the new table with the same structure and constraints, you can then insert data from the existing table into the new table. You can do this by using the INSERT INTO SELECT statement, like this: 

INSERT INTO new_table SELECT * FROM existing_table;
SQL

Remember to replace new_table and existing_table with your actual table names.

This process ensures that the new table is a complete copy of the existing table, including both the data and the constraints.

Tags: