In SQL, the SELECT INTO
statement is used to create a new table based on the result set of a SELECT
query. It is commonly used to copy data from one table to another or to create a new table with specific columns and data based on a query result.
Here's the basic syntax of the SELECT INTO
statement:
SELECT column1, column2, ...
INTO new_table
FROM source_table
WHERE condition;
In this syntax:
column1, column2, ...
: Columns you want to select data from.new_table
: The name of the new table that will be created.source_table
: The name of the table from which you want to retrieve data.condition
: An optional condition to filter the rows selected from the source table.Example:
Let's say we have a table named employees
with columns employee_id
, first_name
, last_name
, and department
, and we want to create a new table named marketing_employees containing only the employees who belong to the 'Marketing' department:
SELECT employee_id, first_name, last_name
INTO marketing_employees
FROM employees
WHERE department = 'Marketing';
This query will create a new table named marketing_employees
containing only the employee_id
, first_name
, and last_name
columns of employees who belong to the 'Marketing' department.
You can also use the SELECT INTO
statement to create a new table with the same structure as an existing table. For example, if we want to create a backup table named employees_backup
with the same structure as the employees
table:
SELECT *
INTO employees_backup
FROM employees
WHERE 1 = 0; -- To ensure that no rows are copied
This query will create a new table named employees_backup
with the same columns as the employees
table, but it won't copy any rows because of the WHERE 1 = 0
condition.
It's important to note that the SELECT INTO
statement creates a new table based on the result set of the SELECT
query. If the new table already exists, the statement will fail. Also, the data types and lengths of the columns in the new table will be determined by the data types and lengths of the columns in the result set.