Is It Possible to Select into a SQL Table Without Creating It First?

When dealing with SQL databases, it can be a common requirement to create a new table and populate it with specific data from an existing table in a single step. One of the most effective methods to achieve this is by using the SELECT INTO statement. This method not only simplifies the process but also maintains standards-specified behavior across most database management systems (DBMS).

Understanding the SELECT INTO Statement

The basic syntax for the SELECT INTO statement is as follows:

SELECT column1, column2,  new_tableFROM existing_tableWHERE condition

Example: Creating a New Table for High-Earning Employees

For instance, if you have an existing table named employees and you want to create a new table named high_earners containing only those employees with a salary above a certain threshold (e.g., 100,000), you could use the following SELECT INTO statement:

SELECT name, salaryINTO high_earnersFROM employeesWHERE salary  100000

After executing this query, the high_earners table will be created with the selected columns and rows from the employees table. This method is particularly useful for creating temporary tables or saving query results to a new table without having to manually define the new table's structure first.

Important Notes About SELECT INTO

It's crucial to understand a few key points regarding the SELECT INTO statement:

The new table will inherit the data types of the selected columns from the existing table. The SELECT INTO statement creates a new table but does not create indexes, constraints, or triggers that might exist on the source table. If the new_table already exists, the query will fail.

Support for SELECT INTO Across Different DBMS

The standards-specified behavior of the SELECT INTO statement can vary depending on the specific DBMS in use. One notable example is the CTAS (Create Table As Select) feature, which was introduced in the SQL:2003 standard. While this feature is supported by popular DBMS like PostgreSQL and SQLite, it might not be available in all systems. A comprehensive comparison of six different SQL implementations was conducted by Treols Arvin, and one of the areas he examined involved copying table structure without data.

It's important to note that the DB versions Treols Arvin focused on may not be the latest and greatest. As such, there may still be some TODOs in the document he referenced. Therefore, regardless of the comparison, it's advisable to check the specific documentation of your DBMS to confirm its support for SELECT INTO or CTAS.

Conclusion

Using the SELECT INTO statement is a powerful tool for efficiently creating and populating new tables based on existing ones. Its utility extends to both temporary and permanent tables, ensuring that you maintain the data's integrity and structure while streamlining your database management tasks.