SQL SELECT INTO Statement

The SQL SELECT INTO statement is used to select data from one table and insert it into a new table.

This statement can be used to create a new table with the same structure as an existing table, or to create a new table with a different structure that includes only the data that you want.

Syntax

The basic syntax of the SELECT INTO statement is as follows:

SELECT column1, column2, ...
INTO new_table
FROM existing_table;

In this example, "column1, column2, ..." is a list of the columns that you want to select from the existing table.

"new_table" is the name of the new table that you want to create, and "existing_table" is the name of the table that you want to select data from.

Creating a New Table with the Same Structure

To create a new table with the same structure as an existing table, you can use the SELECT INTO statement without specifying any column names.

For example:

SELECT *
INTO new_table
FROM existing_table;

Creating a New Table with a Different Structure

To create a new table with a different structure, you can use the SELECT INTO statement and specify the columns that you want to select.

For example, if you want to create a new table that includes only the "id" and "name" columns from the "existing_table", you can use the following query:

SELECT id, name
INTO new_table
FROM existing_table;

Limitations

One thing to keep in mind when using the SELECT INTO statement is that it can only be used to create a new table, not to insert data into an existing table.

Additionally, some databases, such as MySQL and PostgreSQL, do not support the SELECT INTO statement.

In those cases, you can use the CREATE TABLE AS statement to create a new table with the same structure and data as an existing table.

Examples

Here is an example of how to use the SELECT INTO statement to create a new table with the same structure as an existing table:

CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(255),
order_date DATE
);

INSERT INTO orders (order_id, customer_name, order_date)
VALUES (1, 'John Smith', '2022-01-01'),
(2, 'Jane Doe', '2022-01-02');

SELECT *
INTO new_orders
FROM orders;

In this example, a table named "orders" is created and two rows are inserted into it.

Then, the SELECT INTO statement is used to create a new table named "new_orders" with the same structure and data as the "orders" table.

In this next example, we can create a new table that only contains the columns id and name from the existing table:

CREATE TABLE people (
id INT,
name VARCHAR(255),
age INT,
address VARCHAR(255)
);

INSERT INTO people (id, name, age, address)
VALUES (1, 'John Smith', 30, 'New York'),
(2, 'Jane Doe', 25, 'Los Angeles');

SELECT id, name
INTO new_people
FROM people;

In this example, a table named "people" is created and two rows are inserted into it.

Then, the SELECT INTO statement is used to create a new table named "new_people" with only the columns "id" and "name" from the "people" table.

Conclusion

The SQL SELECT INTO statement is a useful tool for creating a new table with the same or different structure as an existing table.

It allows you to easily copy data from one table to another, without having to manually create the new table and insert the data.

It's important to keep in mind that SELECT INTO statement can only be used to create a new table and it not supported by all databases.

In those cases, you can use the CREATE TABLE AS statement to achieve the same result.

❮ Previous Next ❯