The SQL SELECT INTO statement is used to create a new table and insert data into it, based on the result of a SELECT statement. The SELECT INTO statement combines the functionality of the SELECT and INSERT statements into a single statement.
The syntax of the SQL SELECT INTO statement is as follows:
SELECT column1, column2, ... INTO new_table_name FROM table_name WHERE condition;
In the above syntax, the “column1”, “column2”, etc. represent the columns to be inserted into the new table. The “new_table_name” is the name of the new table to be created. The “table_name” is the name of the table from which the data is being selected, and the “condition” in the “WHERE” clause is used to filter the rows to be inserted into the new table.
Consider the following table, “employees”:
Now, if we want to create a new table “high_salary_employees” and insert the names and salaries of all employees with a salary greater than 6000, we can use the following SQL statement:
SELECT Name, Salary INTO high_salary_employees FROM employees WHERE Salary > 6000;
After executing the above SQL statement, a new table “high_salary_employees” will be created with the following data:
It is important to note that the SELECT INTO statement will create a new table and insert data into it, but it will not copy any constraints or indexes associated with the original table. If these are necessary, they must be added to the new table after it is created.
Limitations of SELECT INTO Statement
The SELECT INTO statement is not supported by all relational database management systems. In some systems, the SELECT INTO statement is only supported within stored procedures, while in others it is not supported at all. Additionally, some systems may limit the use of the SELECT INTO statement to specific users or roles.
Another limitation of the SELECT INTO statement is that it creates a new table with the exact structure and data of the result of the SELECT statement. This can result in large amounts of unnecessary data being inserted into the new table, if the SELECT statement returns a large result set. In such cases, it is often more efficient to create the new table with the desired structure beforehand, and then use an INSERT INTO statement to insert the data into the new table.
The SQL SELECT INTO statement is a useful tool for creating a new table and inserting data into it based on the result of a SELECT statement. However, it is important to be aware of its limitations and to choose the appropriate method for creating and inserting data into a table based on the specific requirements and constraints of your project.
In conclusion, the SELECT INTO statement can be a convenient and efficient way to create a new table and insert data into it, but it should be used with caution and with an understanding of its limitations.