SQL INSERT INTO...SELECT Statement

Introduction

In this chapter, we will learn how to use the INSERT INTO...SELECT statement in SQL. This statement is used to copy data from one table and insert it into another table. It is useful for copying data between tables, transforming data, and performing bulk inserts. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the INSERT INTO...SELECT statement effectively.

What is the INSERT INTO...SELECT Statement?

The INSERT INTO...SELECT statement allows you to insert data into a table by selecting data from another table. This can be used to copy data, combine data from multiple tables, or insert data based on complex queries.

Syntax for INSERT INTO...SELECT

Basic Syntax

INSERT INTO table_name1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table_name2
WHERE condition;

• table_name1: The name of the table where you want to insert data.
• column1, column2, ...: The columns in the destination table.
• table_name2: The name of the table from which you want to select data.
• condition: The condition that must be met for a row to be selected (optional).

Example

Assume we have two tables, employees and employees_backup, and we want to copy data from employees to employees_backup.

INSERT INTO employees_backup (first_name, last_name, email, department_id)
SELECT first_name, last_name, email, department_id
FROM employees
WHERE department_id = 1;

This command copies all rows from the employees table where department_id is 1 into the employees_backup table.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables, employees and employees_backup.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

CREATE TABLE employees_backup (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

2. Insert Sample Data into employees

INSERT INTO employees (first_name, last_name, email, department_id)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1),
('Sita', 'Patel', 'sita.patel@example.com', 2),
('Arjun', 'Singh', 'arjun.singh@example.com', 1),
('Priya', 'Sharma', 'priya.sharma@example.com', 2),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3);

3. Insert Data into employees_backup from employees

To copy all employees from department_id 1 into the employees_backup table:

INSERT INTO employees_backup (first_name, last_name, email, department_id)
SELECT first_name, last_name, email, department_id
FROM employees
WHERE department_id = 1;

4. Verify the Insertion

To verify that the rows were inserted correctly, we can use the SELECT statement to retrieve all rows from the employees_backup table.

SELECT * FROM employees_backup;

Output

id	first_name	last_name	email	department_id
1	Ramesh	Kumar	ramesh.kumar@example.com	1
2	Arjun	Singh	arjun.singh@example.com	1

Using INSERT INTO...SELECT Without Specifying Columns

If both tables have the same structure, you can omit the column names.

Example

INSERT INTO employees_backup
SELECT *
FROM employees
WHERE department_id = 2;

Verify the Insertion

SELECT * FROM employees_backup;

Output

id	first_name	last_name	email	department_id
1	Ramesh	Kumar	ramesh.kumar@example.com	1
2	Arjun	Singh	arjun.singh@example.com	1
3	Sita	Patel	sita.patel@example.com	2
4	Priya	Sharma	priyasharma@example.com	2

Using INSERT INTO...SELECT with JOIN

You can also use the INSERT INTO...SELECT statement with a JOIN to combine data from multiple tables.

Example

Assume we have another table named departments:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

To insert data into employees_backup with department names:

INSERT INTO employees_backup (first_name, last_name, email, department_id)
SELECT e.first_name, e.last_name, e.email, d.department_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Finance';

Verify the Insertion

SELECT * FROM employees_backup;

Output

id	first_name	last_name	email	department_id
1	Ramesh	Kumar	ramesh.kumar@example.com	1
2	Arjun	Singh	arjun.singh@example.com	1
3	Sita	Patel	sita.patel@example.com	2
4	Priya	Sharma	priyasharma@example.com	2

Conclusion

The INSERT INTO...SELECT statement is used for copying and transforming data between tables in SQL. This chapter covered the basic syntax, inserting data with and without specifying columns, and using joins to insert data from multiple tables. Understanding how to use the INSERT INTO...SELECT statement effectively will enhance your ability to manage and manipulate data in your database.