The INSERT statement is used to add new rows into a table.

1.Basic Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  • table_name → Name of the table
  • (column1, column2, …) → Columns to insert data into
  • VALUES → The values corresponding to columns
  • Columns must match the values in number and data type.

2.Example Table

Employees Table

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

3.Insert a Single Row

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES (3, 'Mike', 'IT', 6000);

Result:

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

4.Insert Multiple Rows

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES
(4, 'Sara', 'HR', 4700),
(5, 'Tom', 'IT', 5200);

✅ Multiple rows can be inserted in one statement.

5.Insert Data Without Specifying Columns

INSERT INTO Employees
VALUES (6, 'Anna', 'Finance', 5500);

Must provide values for all columns in the exact order.

⚠ Not recommended; better to specify columns.

6.Insert Data from Another Table

INSERT INTO EmployeesBackup (EmployeeID, Name, Department, Salary)
SELECT EmployeeID, Name, Department, Salary
FROM Employees
WHERE Department = 'IT';
  • Copies selected rows from Employees into EmployeesBackup

7.Insert with DEFAULT Values

If a column has a default value:

INSERT INTO Employees (EmployeeID, Name, Department)
VALUES (7, 'Alex', 'Marketing');
  • Salary will use the default value (if defined)

8.Insert Using SELECT (Advanced)

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
SELECT EmployeeID + 100, Name, Department, Salary * 1.1
FROM Employees
WHERE Department = 'IT';
  • Can transform data during insert

9.Identity Column Consideration

If EmployeeID is an IDENTITY column:

INSERT INTO Employees (Name, Department, Salary)
VALUES ('Sam', 'IT', 5800);
  • SQL Server automatically generates EmployeeID
  • To insert explicit values into IDENTITY, use:
SET IDENTITY_INSERT Employees ON;

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES (10, 'Lily', 'HR', 6000);

SET IDENTITY_INSERT Employees OFF;

Categorized in:

SQL Server,