Day 25 – SQL SELECT INTO Statement
The SQL SELECT INTO statement is used to create a new table and populate it with data from an existing table. The new table is created based on the columns in the SELECT statement and the data is inserted into the new table from the source table.
The syntax for the SELECT INTO statement is as follows:
SELECT column1, column2, column3, ... INTO new_table FROM source_table WHERE condition;
In this syntax, the SELECT statement specifies the columns to be copied from the source_table into the new_table. The INTO clause specifies the name of the new table that will be created. The WHERE clause is optional and is used to filter the data that will be copied to the new table based on a specific condition.
It is important to note that the SELECT INTO statement creates a new table with the specified columns and data, and therefore, it should only be used if you want to create a new table with a specific set of columns and data. If you want to copy data from one table to another existing table, you should use the INSERT INTO statement instead.
Additionally, it is important to make sure that the new table does not already exist in the database, or else the SELECT INTO statement will fail.
Suppose you have a table named “orders” that contains information about customer orders, including order ID, customer ID, order date, and order amount. You want to create a new table named “recent_orders” that contains only the orders placed in the last 30 days.
SELECT order_id, customer_id, order_date, order_amount INTO recent_orders FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE());
In this example, the SELECT INTO statement creates a new table named “recent_orders” and copies the columns “order_id,” “customer_id,” “order_date,” and “order_amount” from the “orders” table. The WHERE clause filters the data to only include orders placed within the last 30 days. The resulting “recent_orders” table will contain only the recent orders data from the “orders” table.