Cannot insert the value NULL into column; column does not allow nulls. INSERT fails.

What it means

The column is declared NOT NULL, and your statement would have stored NULL in it. SQL Server stops the statement, and none of its rows are written. The message names the column and the table (with its database), and the last word says what was running:

• INSERT fails. for an INSERT (or the insert part of a MERGE)
• UPDATE fails. for an UPDATE, and also for ALTER TABLE … ALTER COLUMN … NOT NULL when existing rows hold NULL

Common causes

1. The column is missing from the INSERT, and has no default. SQL Server fills an omitted column with its default, or NULL if there isn’t one.
2. An explicit NULL where a default exists. A default is used only when the column is left out (or you write DEFAULT). VALUES (…, NULL) stores NULL, and fails on a NOT NULL column even with a default. ORMs and data-access code often send NULL for every property you didn’t set.
3. NULLs in the source of an INSERT … SELECT or an import: an outer join that found no match, a blank field in a CSV file.
4. An UPDATE that sets NULL, directly or from a subquery that found nothing.
5. Making a column NOT NULL while rows still hold NULL, with ALTER TABLE … ALTER COLUMN.
6. A column created without saying NULL or NOT NULL. Its nullability then comes from the session that created it. Microsoft’s ODBC and OLE DB drivers turn ANSI_NULL_DFLT_ON on when they connect, so such a column allows NULL; in a session with it off, the same CREATE TABLE makes a NOT NULL column.

Adding a new NOT NULL column without a default to a table that has rows fails with a different number, 4901 (ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, …).

How to fix it

Send a value, or leave the column out

Include the column with a real value. If the column has a default and you want it, leave the column out of the INSERT, or write DEFAULT:

INSERT INTO dbo.accounts (email, status) VALUES (N'grace@example.com', DEFAULT);

To see which columns need a value and which have defaults:

SELECT c.name, c.is_nullable, dc.definition AS default_value
FROM sys.columns AS c
LEFT JOIN sys.default_constraints AS dc ON dc.object_id = c.default_object_id
WHERE c.object_id = OBJECT_ID('dbo.accounts');

Replace NULLs on the way in

For INSERT … SELECT and imports, give missing values a fallback:

INSERT INTO dbo.accounts (email, status)
SELECT s.email, COALESCE(s.status, 'active')
FROM staging.accounts AS s;

Or filter out the rows that can’t be loaded (WHERE s.email IS NOT NULL) and deal with them separately.

Add a default, or allow NULL

If the column should have a value when none is given, add a named default:

ALTER TABLE dbo.accounts ADD CONSTRAINT DF_accounts_status DEFAULT ('active') FOR status;

This helps only when the column is left out of the INSERT; it doesn’t replace an explicit NULL. If NULL is a legitimate value, allow it:

ALTER TABLE dbo.accounts ALTER COLUMN note nvarchar(100) NULL;

Write the column’s full definition in ALTER COLUMN, collation included: what you leave out isn’t kept. In the test below, ALTER COLUMN a varchar(20) on a NOT NULL column with a case-sensitive collation made it nullable and gave it the database’s default collation.

Making a column NOT NULL

Fill the NULLs first, then change the column, in one transaction:

BEGIN TRAN;
UPDATE dbo.accounts SET note = N'' WHERE note IS NULL;
ALTER TABLE dbo.accounts ALTER COLUMN note nvarchar(100) NOT NULL;
COMMIT;

To add a new NOT NULL column to a table with rows, give it a default; SQL Server fills it in for the existing rows:

ALTER TABLE dbo.accounts ADD country char(2) NOT NULL CONSTRAINT DF_accounts_country DEFAULT ('GB');

Always write NULL or NOT NULL

In CREATE TABLE and ALTER TABLE, state each column’s nullability, so it doesn’t depend on the settings of whatever ran the script.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, in a scratch schema:

CREATE TABLE seo_sqlerr_data.accounts (
  id int IDENTITY PRIMARY KEY,
  email nvarchar(200) NOT NULL,
  status varchar(20) NOT NULL CONSTRAINT DF_accounts_status DEFAULT ('active'),
  note nvarchar(100) NULL
);
INSERT INTO seo_sqlerr_data.accounts (note) VALUES (N'no email');
INSERT INTO seo_sqlerr_data.accounts (email, status) VALUES (N'ada@example.com', NULL);

Msg 515, Level 16, State 2, Server 7732422b7f56, Line 1
Cannot insert the value NULL into column 'email', table 'inlet.seo_sqlerr_data.accounts'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Server 7732422b7f56, Line 1
Cannot insert the value NULL into column 'status', table 'inlet.seo_sqlerr_data.accounts'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The second failed although status has a default. Leaving status out, or writing DEFAULT, stored active. Setting email to NULL in an UPDATE, and making note NOT NULL while a row had NULL in it, both ended UPDATE fails.:

Msg 515, Level 16, State 2, Server 7732422b7f56, Line 1
Cannot insert the value NULL into column 'note', table 'inlet.seo_sqlerr_data.accounts'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

After setting the NULLs to '', the ALTER COLUMN worked. Adding country char(2) NOT NULL without a default gave error 4901; with DEFAULT ('GB') it filled GB into both existing rows. In a session with SET ANSI_NULL_DFLT_ON OFF, CREATE TABLE #t (a int, b int) made both columns NOT NULL, and INSERT INTO #t (a) VALUES (1) failed with 515 on column b; with sqlcmd’s own settings, the same columns allowed NULL. A column a varchar(10) COLLATE Latin1_General_CS_AS NOT NULL altered with ALTER COLUMN a varchar(20) came out nullable, with the database’s collation SQL_Latin1_General_CP1_CI_AS. SQL Server 2019 and 2025 printed the same messages.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 515, state 2, severity 16 and links to this page. The structure designer writes the T-SQL for column changes, with named default constraints, and keeps the column’s collation, so you can add a default or change a column’s nullability without writing the ALTER TABLE yourself. Grid edits are staged until you commit (⌘S), and Review shows what will run first.