SQL SELECT INTO

The SELECT INTO statement in SQL is used to create a new table by copying data from an existing table. This operation will create a new table and insert the selected data into it. This is different from the INSERT INTO statement, which inserts data into an already existing table.

Here’s the basic syntax of SELECT INTO:

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

Example 1: Basic Usage

Let’s say we have a table called employees with the following data:

We want to create a new table hr_employees that contains only employees from the HR department.

SELECT employee_id, name
INTO hr_employees
FROM employees
WHERE department = 'HR';

After executing this SQL command, the hr_employees table will be created with the following data:

Example 2: Select and Transform Data

Suppose you have a table sales with the following data:

You want to create a new table high_value_sales that contains sales with an amount greater than 100.

SELECT sale_id, product_name, amount
INTO high_value_sales
FROM sales
WHERE amount > 100;

The high_value_sales table will have:

Key Points:

• New Table Creation: SELECT INTO creates a new table and populates it with data.
• Data Types: The new table’s columns have the same data types as the columns in the original table.
• Index and Constraints: The new table does not inherit indexes, constraints, or triggers from the original table. You’ll need to add those separately if needed.

Use SELECT INTO when you want to make a copy of a dataset or create a new table for reporting or further processing based on existing data.