This article will explain the complete overview of a SELECT INTO statement in SQL Server. We will use this statement to add data into tables. When we use this statement, it will first create a new table in the default filegroup before inserting the records selected by the SELECT statement into that new table. It is to note that the constraints from the source table are not copied to the destination table.
Syntax:
The following are the syntax that illustrates the SELECT INTO statement:
SELECT columns_list
INTO new_table_name
FROM source_table_name
[Where conditions];Parameters
The parameter explanation of this statement is given below:
- columns_list: It indicates the column names that we want to retrieve from the source table and insert into a new table.
- new_table_name: It indicates the new table name that must be unique. This statement creates a new table with the specified columns in the columns_list.
- source_table_name: It indicates the name of a table from which we want to get records. Here we can also specify several tables with proper JOIN clauses.
- WHERE condition: It is an optional clause and uses to copy the partial data from the source table.
SELECT INTO Example
Let us understand how the SELECT INTO statement works in SQL Server with some examples. Suppose we have a table “employee” that contains the following data:
1. If we want to copy all records of the employee table into the backup_employee table, we need to use the SELECT INTO statement as follows:
SELECT * INTO backup_employee FROM employee; We can verify whether employee data is successfully inserted into the backup_employee table as follows:
SELECT * FROM backup_employee; Here is the output:
2. If we want to copy only some rows of the employee table into the backup_employee table, we need to use the SELECT INTO statement with WHERE clause as follows:
SELECT * INTO backup_employee FROM employee WHERE salary>30000; We can verify whether employee data is successfully inserted into the backup_employee table using the SELECT statement. Here is the output:
3. If we want to copy only some columns of the employee table into the backup_employee table, we need to specify the desired column names in the SELECT INTO statement as follows:
SELECT name, occupation, salary
INTO backup_employee FROM employee;We can verify whether employee data is successfully inserted into the backup_employee table using the SELECT statement. Here is the output:
SELECT INTO Insert Data from multiple tables
SQL Server enables us to use the JOIN clause with the SELECT INTO statement. It helps us retrieve records from more than one table and then insert them into the new table. Here, we are using the INNER JOIN for joining the two tables using the id column.
Consider the following tables name customer and orders having the following data:
Suppose we want to copy columns like name, email, item, price, and purchase date from the customer and orders table into the customer_order table. We can do this by specifying the column names in the SELECT INTO statement with the JOIN clause as follows:
SELECT cust.c_name, cust.email, O.item, O.price, O.prchase_date
INTO customer_orders FROM customer AS cust
Inner JOIN orders AS O ON cust.id = O.order_id;We can verify whether customer and orders table data is successfully inserted into the customer_order table using the SELECT statement. Here is the output:
SELECT INTO vs. INSERT INTO SELECT
Both the statements can be used to move data from one table into another table. Since they provide similar functionality, both statements have shared some differences that may affect our decision as to which one to choose.
The following points explain the differences between them:
1. We can use the INSERT INTO select statements only when the target table exists in the database before copying data from the source to the target table. On the other hand, the SELECT INTO statement does not require a target table to exist in our database before copying data from the source table. It automatically creates a target table whenever executed.
INSERT INTO tempTable SELECT * FROM employee; This statement through an error because tempTable does not exist in the database. Here is the output:
2. Another difference that distinguishes them is related to the structure of the target tables. Since the SELECT INTO statement created the table structure automatically, which will be the same structure as the source table. It may lead to a problem while inserting the data. The below example illustrates this point.
Example:
The above-created customer_order table contains a price column with length 8. The SELECT INTO statement will generate the target table with the exact column definition as of the source table. When we try to insert data in the price column more than their size, we will get an error, or binary data would be truncated.
Leave a Reply