The SELECT INTO statement in SQL is used to create a new table and insert data from an existing table. This statement allows you to select data from one or more tables and directly store it in a new table.
Here's the general syntax:
SELECT column1, column2, ...
INTO new_table_name
FROM existing_table_name
WHERE condition;
Key Points:
- new_table_name: This is the name of the new table that will be created.
- existing_table_name: This is the name of the table from which you are selecting data.
- column1, column2, ...: Specify the columns you want to select. You can also use * to select all columns.
- condition: This part is optional. You can specify a WHERE clause to filter the rows that you want to insert into the new table.
Example:
Assume you have an existing table named Employees and you want to create a new table named HighEarners that contains only the employees with a salary greater than $80,000.
Here's how you can do this with the SELECT INTO statement:
SELECT EmployeeID, FirstName, LastName, Salary
INTO HighEarners
FROM Employees
WHERE Salary > 80000;
Explanation of the Example:
- This will create a new table named HighEarners.
- The new table will have the columns EmployeeID, FirstName, LastName, and Salary.
- The rows that are inserted into the HighEarners table will be those where the Salary is greater than $80,000.
Considerations:
1. Permissions: Ensure that you have the necessary permissions to create a table in the database.
2. Data Types: The new table's columns will inherit data types from the selected columns.
3. Existing Tables: The SELECT INTO statement cannot be used if the new table already exists. You'll need to drop the existing table before re-creating it or use INSERT INTO instead.
4. Indexes and Constraints: The new table will not inherit indexes, primary keys, or constraints from the existing table. You may need to add these manually after creation if required.
Using the SELECT INTO statement is a straightforward and effective way to create a new table while selecting data from an existing table directly in SQL.