The INSERT INTO ... SELECT statement is used to insert data into a table by selecting it from one or more other tables.

1.Basic Syntax

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
  • target_table → Table you want to insert into
  • source_table → Table you are selecting data from
  • Columns must match in number and compatible data types

2.Example Tables

Employees_2024

EmployeeID Name Department Salary
1 John IT 5000
2 Jane HR 4500
3 Mike IT 6000

Employees_Archive (empty)

EmployeeID Name Department Salary

3.Insert Data from Another Table

INSERT INTO Employees_Archive (EmployeeID, Name, Department, Salary)
SELECT EmployeeID, Name, Department, Salary
FROM Employees_2024
WHERE Department = 'IT';

Result:

Employees_Archive

EmployeeID Name Department Salary
1 John IT 5000
3 Mike IT 6000

Only IT department employees are copied.

4.Insert Data from a Join

INSERT INTO EmployeeSummary (EmployeeID, Name, TotalSalary)
SELECT e.EmployeeID, e.Name, SUM(s.Amount)
FROM Employees e
INNER JOIN Salaries s
    ON e.EmployeeID = s.EmployeeID
GROUP BY e.EmployeeID, e.Name;
  • Can aggregate data while inserting into another table

5.Insert Data with Transformations

INSERT INTO Employees_Archive (EmployeeID, Name, Department, Salary)
SELECT EmployeeID + 100, Name, Department, Salary * 1.1
FROM Employees_2024;
  • Can modify or calculate columns during insert

6.Insert from Multiple Tables (Using Joins)

INSERT INTO SalesSummary (CustomerID, TotalAmount)
SELECT c.CustomerID, SUM(s.Amount)
FROM Customers c
INNER JOIN Sales s
    ON c.CustomerID = s.CustomerID
GROUP BY c.CustomerID;
  • Combines multiple source tables and inserts into target table

7.Insert Using SELECT Without Specifying Columns

INSERT INTO Employees_Archive
SELECT * FROM Employees_2024
WHERE Department = 'HR';

⚠ Must match all columns and order exactly.

8.Identity Column Consideration

If target table has an IDENTITY column:

SET IDENTITY_INSERT Employees_Archive ON;

INSERT INTO Employees_Archive (EmployeeID, Name, Department, Salary)
SELECT EmployeeID, Name, Department, Salary
FROM Employees_2024
WHERE Department = 'IT';

SET IDENTITY_INSERT Employees_Archive OFF;

Categorized in:

SQL Server,