SQL Server INSERT INTO SELECT

It is not easy to insert data of one table into another in the same or different database using the INSERT query manually. However, SQL Server provides an INSERT INTO SELECT statement to optimize this process. The INSERT INTO statement populates the tables quickly. In this section, we are going to learn the INSERT INTO SELECT command, syntax, and its use cases.

The INSERT INTO SELECT statement is used to insert data into a table where data comes from a SELECT query. In other words, this query copies data from one table and inserts them in the other table. We should remember the below points before using this statement:

• A table must have existed in the database in which we are going to insert data.
• Both the source and target tables must have the same data types.
• The existing records in the target table should be unaffected.

When we need to copy data from one table to another or summarize data from several tables into a single table, this command comes in handy.

Syntax

Generally, we used the INSERT command to add one or more records to a table with the VALUES clause to list column values. See the below syntax:

To copy one table data into another, we need to use the INSERT INTO SELECT statement in SQL Server as follows:

Here, we used a SELECT statement instead of using the VALUES clause that fetches data from one or more tables.

Parameter Explanation

We can see the following parameters in the INSERT INTO SELECT statement:

• destination_table: This parameter indicates the name of a table in which we are going to insert data.
• source_table: This parameter indicates the name of a source table from where we are going to fetch data.
• column_list: It represents the column names of the table.
• condition: It is an optional parameter that is used in filtering the table data.
• TOP: It's an optional clause that specifies how many rows from the query should be inserted into the target table.
• PERCENT: It is an optional clause for inserting the percent of rows.

INSERT INTO SELECT Example

Let us understand how to use the INSERT INTO SELECT command in SQL Server with the help of an example. To do this, we first need to create a table named 'Student' in the specified database using the statement given below:

Next, we will add some values into this table as follows:

We can use the SELECT statement to display the table. We will see the following data in the table:

Now, we will create a table named student_info for the demonstration of the destination table:

1: Insert all data from one table to another

Suppose we want to insert all data of the student table into the student_info table. We can do this by using the following syntax:

We can verify the insert operation using the SELECT statement. We will get the below output:

2. Insert some data from one table to another

Sometimes, we need to insert only some records into another table. We can do this by filtering the number of rows returned from the query with the help of a WHERE clause.

The following statement fetches the male student from the 'Student' table and inserts them into the student_info table:

Using the SELECT statement, we can see that the student_info table have only male records:

3: Insert the top N of rows

The TOP clause specifies how many rows from the query should be inserted into the target table. We can do this by first truncating all rows from the student_info table using this statement:

Second, we will use the below statement to insert the top 3 students sorted by their total marks:

Using the SELECT statement, we can see that the student_info table have only three records:

4: Insert the top percent of rows

The percent is used for inserting the percent of rows in a table when we do not want to use an absolute number of rows. Its value should be in a range between 0 and 100. For example, if we set this value to 50, it will allow us to retrieve half of the rows in a table. We can do this by first truncating all rows from the student_info table using this statement:

Second, we will use the below statement to insert the 30 percent of rows in the table students sorted by their total marks:

Using the SELECT statement, we can see that the student_info table have only three records:

5. INSERT INTO SELECT Statement with JOIN

SQL Server also enables us to use the INSERT INTO SELECT statement to retrieve data from multiple tables. Suppose we want to retrieve data from customer and orders tables into another table custorder_orders. We can do this by selecting columns present in both tables and then uses the INSERT INTO SELECT statement for insertion to the other table. Here we will use the INNER JOIN for joining the two tables using the id column.

We will execute the SELECT command to verify whether the data inserted the selected data into the destination table or not. Here is the output: