LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 1518 min read

INSERT Statement

Learn how to add new rows to a SQL Server table using INSERT, including single-row inserts, multi-row inserts, and inserting into specific columns.

Introduction

Once you have created a table, the next step is to put data into it. In SQL Server, the INSERT statement is the T-SQL command used to add new rows to a table. Whether you are adding a single customer record or loading hundreds of rows at once, INSERT is the tool for the job. In this lesson you will learn the different forms of INSERT and how to use them safely and efficiently.

What You Will Learn
  • The basic syntax of the INSERT statement.
  • How to insert values into specific columns only.
  • How to insert multiple rows in a single statement.
  • How INSERT interacts with IDENTITY columns.

Basic INSERT Syntax

The simplest form of INSERT specifies the table name, the list of columns, and the values to insert. The values must appear in the same order as the columns you list, and their data types must be compatible.

Basic INSERT
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    Department VARCHAR(50),
    Salary     DECIMAL(10,2),
    HireDate   DATE
);


INSERT INTO Employees (FirstName, LastName, Department, Salary, HireDate)
VALUES ('Amit', 'Sharma', 'Engineering', 65000.00, '2023-04-10');


SELECT * FROM Employees;
Result

Click Run to see what this code prints.

Notice that EmployeeID was not included in the column list or the VALUES list. Because it is an IDENTITY column, SQL Server automatically generates the value for us.

Inserting Into Specific Columns

You do not always need to supply a value for every column. Columns that allow NULL, or that have a DEFAULT constraint, can be safely left out of the INSERT statement. Any column you omit will receive its default value, or NULL if no default is defined.

Partial Column Insert
-- Department and HireDate are left out
INSERT INTO Employees (FirstName, LastName, Salary)
VALUES ('Priya', 'Nair', 58000.00);


SELECT * FROM Employees WHERE FirstName = 'Priya';
Result

Click Run to see what this code prints.

Column Order Matters

When you specify a column list, the VALUES must line up with it position by position. Always list column names explicitly rather than relying on the table's physical column order — it makes your INSERT statements resilient to future ALTER TABLE changes.

Inserting Multiple Rows

SQL Server allows you to insert several rows with a single INSERT statement by separating each row's values with a comma. This is faster and more concise than writing a separate INSERT statement for every row, and it is executed as a single batch operation.

Multi-Row INSERT
INSERT INTO Employees (FirstName, LastName, Department, Salary, HireDate)
VALUES
    ('Rahul', 'Verma', 'Sales', 52000.00, '2022-11-01'),
    ('Sneha', 'Iyer', 'Marketing', 55000.00, '2023-01-15'),
    ('Karan', 'Mehta', 'Engineering', 71000.00, '2021-06-20');


SELECT EmployeeID, FirstName, Department FROM Employees;
Result

Click Run to see what this code prints.

INSERT with IDENTITY Columns

Because EmployeeID is an IDENTITY column, SQL Server generates it automatically and rejects any attempt to insert a value into it directly. If you genuinely need to insert an explicit value into an IDENTITY column — for example while migrating data — you must temporarily turn on IDENTITY_INSERT.

Explicit IDENTITY Insert
SET IDENTITY_INSERT Employees ON;


INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES (100, 'Vikram', 'Rao', 'Finance', 68000.00, '2020-03-05');


SET IDENTITY_INSERT Employees OFF;
Only One Table at a Time

Only one table per session can have IDENTITY_INSERT set to ON at any given moment. Always turn it back OFF immediately after your explicit insert to avoid confusing errors later in the script.

Common Mistakes

Avoid These Mistakes
  • Forgetting to list column names and relying on physical column order, which breaks silently after an ALTER TABLE.
  • Trying to insert a value into an IDENTITY column without enabling IDENTITY_INSERT first.
  • Mismatching the number of values with the number of columns listed, which raises a column count error.
  • Inserting a string into a numeric or date column without proper formatting, causing a conversion error.
  • Omitting a NOT NULL column that has no default, which fails with a constraint violation.

Best Practices

  • Always specify the column list explicitly instead of relying on table column order.
  • Use multi-row INSERT statements when loading several related rows at once for better performance.
  • Wrap large batches of inserts in an explicit transaction so you can roll back on failure.
  • Avoid using IDENTITY_INSERT unless you have a genuine data migration or archival reason.
  • Validate incoming data types before inserting to avoid implicit conversion surprises.

Frequently Asked Questions

Yes. You can use INSERT INTO ... SELECT to copy rows from one table (or query result) into another, without listing literal VALUES.

SQL Server raises a primary key violation error and the insert fails. Primary key values must be unique across the table.

The multi-row VALUES syntax supports up to 1,000 rows per statement. For larger loads, use multiple statements, INSERT ... SELECT, or a bulk load tool.

No. You only need to supply values for columns that do not have a default and do not allow NULL. IDENTITY columns should also be omitted under normal circumstances.

Key Takeaways

  • INSERT INTO adds new rows to a table.
  • Always list column names explicitly for clarity and safety.
  • Columns can be omitted if they allow NULL or have a DEFAULT.
  • Multiple rows can be inserted in one statement using comma-separated VALUES lists.
  • IDENTITY_INSERT must be turned ON to explicitly supply IDENTITY column values.

Summary

The INSERT statement is how you populate SQL Server tables with data, whether one row at a time or in bulk. Now that you know how to add data, the natural next step is learning how to retrieve it — which is exactly what the SELECT statement is for.

Next Lesson →

SELECT Statement