SQL SELECT INTO Statement – Tutorial with Examples

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.

Syntax

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.

Example

Consider the following table, “employees”:

ID Name Salary
1 John Doe 5000
2 Jane Doe 5500
3 Bob Smith 6000
4 Alice Johnson 6500

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:

Name Salary
Bob Smith 6000
Alice Johnson 6500

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.

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *