# Inserting Data

Add rows to a table with INSERT INTO, name the columns you fill, let identity columns number new rows, and respect the table's keys.

Apprentice · T-SQL · SQL · Updated November 3, 2026

## After this lesson you can

- Add rows with INSERT INTO, naming the columns you fill
- Explain how identity columns, required columns and foreign keys affect an insert

## Key takeaways

- `INSERT INTO` names the columns you fill, then `VALUES` gives their values in the same order. Text and dates go in single quotes; numbers don't.
- Identity columns such as `ProductId` number themselves. Read `SCOPE_IDENTITY()` straight after the insert to learn the new id; never guess it.
- A column you leave out gets its default or `NULL`, so leaving out a required column such as `ProductName` makes the insert fail.
- A primary key can't be repeated, and a foreign key must point to a row that exists, so insert the row it points to first.
- A single quote inside text is written twice: `'Joe''s Couriers'` stores `Joe's Couriers`.

`SELECT` reads data. To add new rows, you use `INSERT`. Changing data is serious work, so this chapter shows each statement together with the habits that keep you out of trouble.

Try every example in the **Northwind** database. If you make a mess, run `Northwind.sql` again to start fresh.

## The shape of an INSERT

```sql
INSERT INTO <table> (<column1>, <column2>, ...)
VALUES (<value1>, <value2>, ...);
```

List the columns you're filling, then the values for them, in the same order. Text and dates go in single quotes; numbers don't.

```sql
INSERT INTO Categories (CategoryName, Description)
VALUES ('Sporting Goods', 'Equipment for team sports, camping and river rafting');
```

SSMS answers in the **Messages** tab:

```text
(1 row affected)
```

Check it with a `SELECT`:

```sql
SELECT CategoryId, CategoryName
FROM Categories
WHERE CategoryName = 'Sporting Goods';
```

## Identity columns number themselves

You didn't give the new category a `CategoryId`. You don't have to, and you can't: `CategoryId` is an **identity** column, and SQL Server gives each new row the next number itself. The same is true of `ProductId`, `OrderId` and `EmployeeId` in Northwind.

To find out which number a new row got, read `SCOPE_IDENTITY()` straight after the insert. Store it in a **variable**, a named value that lasts while the script runs:

```sql
DECLARE @NewProductId int;

INSERT INTO Products (ProductName, CategoryId, UnitPrice, UnitsInStock)
VALUES ('Hot Chocolate', 1, 5.99, 50);

SET @NewProductId = SCOPE_IDENTITY();
SELECT @NewProductId AS NewProductId;
```

On a fresh Northwind the new product is number 78, the next after the 77 that exist. The number can be higher on your computer: an identity number is used up even when an insert is undone, so gaps are normal. Never guess an id; ask for it.

T-SQL variables start with `@`. `DECLARE` creates one with a type, and `SET` gives it a value.

## Not every column needs a value

In the example above, `SupplierId`, `QuantityPerUnit` and others were left out. A column you don't fill gets its **default** (`UnitsOnOrder` defaults to 0) or, if it has none, `NULL`. That only works for columns that allow it. Leave out a required column, and the insert fails:

```sql
INSERT INTO Products (CategoryId, UnitPrice)
VALUES (1, 5.99);
```

```text
Cannot insert the value NULL into column 'ProductName', table 'Northwind.dbo.Products'; column does not allow nulls. INSERT fails.
```

## Keys protect the data

A table's **primary key** identifies each row, so it must be unique. Customers have a five-letter `CustomerId` that you choose yourself, and you can't reuse one:

```sql
INSERT INTO Customers (CustomerId, CompanyName)
VALUES ('ALFKI', 'Another Alfreds');
```

```text
Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (ALFKI).
```

A **foreign key** links a row to a row in another table. Every product's `CategoryId` must be a category that exists. There's no category 99, so:

```sql
INSERT INTO Products (ProductName, CategoryId, UnitPrice)
VALUES ('Hot Chocolate', 99, 5.99);
```

```text
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Products_Categories". The conflict occurred in database "Northwind", table "dbo.Categories", column 'CategoryId'.
```

These errors are the database protecting you: it won't store a duplicate customer, or a product in a category that doesn't exist. Insert the row a foreign key points to **first**, then the rows that point to it.

## Several rows at once

One `INSERT` can add several rows: separate each row's values with a comma.

```sql
INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Rapid Delivery', '(503) 555-0110')
    , ('Mountain Freight', '(503) 555-0120');
```

```text
(2 rows affected)
```

## A quote inside text

Text goes in single quotes, so a single quote inside the text is written twice:

```sql
INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Joe''s Couriers', '(503) 555-0130');
```

That stores `Joe's Couriers`.

## Check yourself

2 questions to try on the lesson page.

## So what

An `INSERT` names its columns, lets identity columns number themselves, and works with the table's keys, not against them. Read each error carefully: it's the database telling you exactly which rule your row broke.

## Key terms

- [INSERT](https://www.ask-codey.com/glossary/insert)
- [VALUES Clause](https://www.ask-codey.com/glossary/values-clause)
- IDENTITY
- SCOPE_IDENTITY
- DECLARE and SET
- [NOT NULL Constraint](https://www.ask-codey.com/glossary/not-null-constraint)

## Cheat sheets

- [Inserting Data](https://www.ask-codey.com/cheat-sheets/modifying-data-in-t-sql-inserting-data-cheat-sheet)

Source: https://www.ask-codey.com/lessons/modifying-data-in-t-sql-inserting-data