How to Create One Table From Another Table in SQL

Creating a table based on the structure and data of an existing table is a common task in database management. This process allows us to replicate a table for backup, testing or data transformation purposes.

SQL provides efficient methods to create one table from another while preserving the schema, data or both. In this article, we will explore various techniques with examples and output in detail.

How to Create One Table From Another Table in SQL?

Creating a new table with only the necessary data can improve performance for specific queries. We will use the below two methods that explain How to Create One Table From Another Table in SQL are defined below:

1. Using CREATE TABLE ... AS
2. Using INSERT INTO ... SELECT with a Predefined Table

Let's setup an environment:

We will use the below employees table to perform all the methods as shown below:

Method 1: Using CREATE TABLE ... AS

The CREATE TABLE ... AS statement allows us to create a new table by copying the structure and data from an existing table.

This method is supported in most relational database systems like PostgreSQL, SQLite, and MySQL (with some variations).

Query:

CREATE TABLE it_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE department = 'IT';

SELECT * FROM it_employees;

Output

Explanation:

• The CREATE TABLE ... AS statement creates a new table called it_employees which contains the employee_id, name and salary columns from the employees table.
• It only includes rows where the department is 'IT'.
• The SELECT * FROM it_employees statement then retrieves all the data from the newly created it_employees table.

Method 2: Using INSERT INTO ... SELECT with a Predefined Table

If we need more control over the structure of the new table, we can first create the table with the desired schema and then populate it with data from the existing table using the INSERT INTO ... SELECT statement.

Query:

Suppose we want to create a new table finance_employees with specific columns and data types and then populate it with data from the employees table:

CREATE TABLE finance_employees (
    employee_id INT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO finance_employees (employee_id, name, salary)
SELECT employee_id, name, salary
FROM employees
WHERE department = 'Finance';

Output:

Explanation:

• The CREATE TABLE statement creates a new table called finance_employees with three columns: employee_id (integer), name (string), and salary (decimal).
• The INSERT INTO ... SELECT statement then populates this new table with data from the employees table, selecting only the employees who belong to the 'Finance' department.
• It copies the employee_id, name, and salary values into the finance_employees table.

Conclusion

Overall, Creating one table from another table in SQL is a versatile operation that can be customize to meet various requirements. Whether you need to copy both structure and data, structure only or a subset of data, SQL provides powerful and efficient tools to achieve your goals. By mastering these techniques, you can enhance your database management and data analysis workflows.