Blog

Let’s delve into the basics of SQL programming, specifically focusing on the INSERT INTO SELECT statement. SQL (Structured Query Language) is a powerful tool used for managing and manipulating data in relational databases. The INSERT INTO SELECT statement allows you to insert data into a table from another table or from the result of a SELECT query.

Here’s a detailed tutorial with sample code snippets for you to understand this concept:

1. Basic Syntax of INSERT INTO SELECT:

INSERT INTO target_table (column1, column2, ...)
SELECT expression1, expression2, ...
FROM source_table
WHERE condition;

• target_table: The name of the table where data will be inserted.
• (column1, column2, ...): Optional. If specified, it indicates the columns in the target table where data will be inserted.
• SELECT expression1, expression2, ...: The SELECT query that retrieves the data to be inserted.
• source_table: The table from which data will be selected.
• WHERE condition: Optional. Specifies conditions for selecting data from the source table.

2. Example Scenarios:

Scenario 1: Inserting All Columns from Source Table into Target Table

Suppose we have two tables: employees and new_employees. We want to insert all records from the employees table into the new_employees table.

-- Creating the new_employees table (assuming it has the same structure as employees)
CREATE TABLE new_employees AS SELECT * FROM employees;

-- Displaying the contents of new_employees table
SELECT * FROM new_employees;

Explanation:

• The CREATE TABLE new_employees AS SELECT * FROM employees; statement creates a new table new_employees with the same structure as employees and inserts all records from employees into new_employees.
• The SELECT * FROM new_employees; statement is used to verify that the data has been successfully inserted into the new_employees table.

Scenario 2: Inserting Specific Columns from Source Table into Target Table

Suppose we want to insert only the name and salary columns from the employees table into the new_employees table.

-- Creating the new_employees table with specific columns
CREATE TABLE new_employees AS 
SELECT name, salary 
FROM employees;

-- Displaying the contents of new_employees table
SELECT * FROM new_employees;

Explanation:

• The CREATE TABLE new_employees AS SELECT name, salary FROM employees; statement creates a new table new_employees with columns name and salary, and inserts data from employees table into these columns.
• The SELECT * FROM new_employees; statement is used to verify that only the specified columns have been inserted into the new_employees table.

Scenario 3: Inserting Data with Conditions

Suppose we want to insert only the employees who belong to the ‘Sales’ department into the new_employees table.

-- Creating the new_employees table with employees from Sales department
CREATE TABLE new_employees AS 
SELECT * 
FROM employees 
WHERE department = 'Sales';

-- Displaying the contents of new_employees table
SELECT * FROM new_employees;

Explanation:

• The CREATE TABLE new_employees AS SELECT * FROM employees WHERE department = 'Sales'; statement creates a new table new_employees and inserts only the records from employees where the department is ‘Sales’.
• The SELECT * FROM new_employees; statement is used to verify that only the employees from the ‘Sales’ department have been inserted into the new_employees table.

Conclusion:

In this tutorial, you’ve learned the basics of using the INSERT INTO SELECT statement in SQL. You can now insert data into a table from another table or from the result of a SELECT query, and apply various conditions to filter the data being inserted. Experiment with different scenarios to further enhance your understanding of SQL programming.