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:
INSERT INTO table_name (column_list) VALUES (value_list); To copy one table data into another, we need to use the INSERT INTO SELECT statement in SQL Server as follows:
INSERT [ TOP ( expression ) [ PERCENT ] ]
INTO destination_table [column_lists]
SELECT column_lists
FROM source_table
WHERE ConditionHere, 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:
CREATE TABLE Student
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(20) NOT NULL,
age INT NOT NULL,
total_marks INT NOT NULL
)Next, we will add some values into this table as follows:
INSERT INTO Student
VALUES ('Jolly Evans', 'Female', 20, 520),
('Josh Butler', 'Male', 22, 645),
('Rose Huges', 'Female', 25, 610),
('Laura Bennet', 'Female', 18, 430),
('Alan Simmons', 'Male', 20, 500),
('Kate Huges', 'Female', 22, 600),
('Joseph Paul', 'Male', 18, 643),
('Antonio Butler', 'Male', 23, 513),
('Diego Bennet', 'Male', 21, 699),
('Elis Simmons', 'Female', 27, 540);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:
CREATE TABLE Student_info
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(20) NOT NULL,
age INT NOT NULL,
total_marks INT NOT NULL
)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
INSERT INTO Student_info(name, gender, age, total_marks)
SELECT name, gender, age, total_marks FROM Student;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:
INSERT INTO Student_info(name, gender, age, total_marks)
SELECT name, gender, age, total_marks FROM Student
WHERE gender = 'male';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:
TRUNCATE TABLE Student_info; Second, we will use the below statement to insert the top 3 students sorted by their total marks:
INSERT TOP (3) INTO Student_info(name, gender, age, total_marks)
SELECT name, gender, age, total_marks FROM Student
ORDER BY 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:
TRUNCATE TABLE Student_info; Second, we will use the below statement to insert the 30 percent of rows in the table students sorted by their total marks:
INSERT TOP (30) PERCENT INTO Student_info(name, gender, age, total_marks)
SELECT name, gender, age, total_marks FROM Student
ORDER BY 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.
INSERT INTO customer_orders(c_name, email, item, price, prchase_date)
SELECT cust.c_name, cust.email, o.item, o.price, o.prchase_date
FROM customer AS cust
INNER JOIN orders AS o
ON o.order_id = cust.id;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:
Leave a Reply