Ошибка cannot insert the value null into column

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column Name', 
table 'Table Name'; column does not allow nulls.  
INSERT fails. The statement has been terminated.

To illustrate, let’s say you have the following table definitions:

CREATE TABLE [dbo].[Users] (
    [UserName]     VARCHAR(10)  NOT NULL,
    [FullName]     VARCHAR(100) NOT NULL,
    [Email]        VARCHAR(100) NOT NULL,
    [Password]     VARCHAR(20)  NOT NULL,
    [CreationDate] DATETIME     NOT NULL DEFAULT(GETDATE())
)

There are three ways that the error can be encountered.  The first way is when a column is not specified as one of the columns in the INSERT clause and that column does not accept NULL values.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com' )

Given this INSERT statement, the [Password] column is not specified in the column list of the INSERT INTO clause.  Since it is not specified, it is assigned a value of NULL. But since the column does not allow NULL values, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Password', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The second way that the error can be encountered is when a NULL value is explicitly assigned to the column that does not allow NULLs.

INSERT INTO [dbo].[Users]([UserName], [FullName], [Email], [Password])
VALUES ( 'MICKEY', 'Mickey Mouse', NULL, 'Minnie' )

As can be seen from this INSERT command, the [Email] column is being assigned a NULL value during the insert but since the column does not allow NULL values, the following error is generated:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Email', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The third possible way that the error is encountered is similar to the second one, which is by explicitly assigning a NULL value to a column, as shown below:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', NULL )

The only difference is that the column being assigned to has a default value (in this case, the default value of the [CreationDate] column is current system date and time as generated by the GETDATE() function).  Since the column has a default value, you would think that if a NULL value is assigned to it, it will assign the default value instead. However, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CreationDate', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Solution / Work Around:

Regardless of the way on how the error is encountered, if a column does not accept NULL values, always assign a value to it when inserting new records to the table.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', GETDATE() )

If the column does not accept NULL values but has a default value assigned to it and you want that the default value be used for the newly inserted record, just do not include that column in the INSERT statement and the default will automatically be assigned to the column.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie' )

In this example, since the [CreationDate] column has a default value of GETDATE(), since it is not included in the column list in the INSERT INTO clause, the default value gets assigned to the column.

Another way of explicitly assigning the default value is by using the reserved word DEFAULT in the VALUES list, as can be seen in the following:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', DEFAULT )

Mad88

23.01.17 — 12:19

Господин ПЖ

1 — 23.01.17 — 12:20

Serg_1960

2 — 23.01.17 — 12:21

ТиИ?

Mad88

3 — 23.01.17 — 12:31

(1) То есть если он не может вставить, мне вставить за него или что? Конкретные предложения будут?

Господин ПЖ

4 — 23.01.17 — 12:32

Serg_1960

5 — 23.01.17 — 12:36

Ёпрст

6 — 23.01.17 — 12:37

(0) Ну посмотри, че за справочник, для начала. И чего он там нулл туда инсёртит

Mad88

7 — 23.01.17 — 13:18

(5) Какие там галочки надо ставить?)

Господин ПЖ

8 — 23.01.17 — 13:19

логическая+физическая

Господин ПЖ

9 — 23.01.17 — 13:20

ругается на справочник — п.э. всякие «итоги» не нужны

Mad88

10 — 23.01.17 — 13:21

Ставил все кроме «Пересчет итогов», ошибка осталась

Господин ПЖ

11 — 23.01.17 — 13:22

кэш продувал? что за поле, почему у тебя судя по всему по разному поле описывается на уровне субд и конфигурации?

Mad88

12 — 23.01.17 — 13:38

(11) Кэш чистиль…)

Serg_1960

13 — 23.01.17 — 21:36

суперйцукен

14 — 23.01.17 — 21:42

(13) На самом деле очень интересно давать полям, переменным, реквизитам наименования типа «Документ», «справочник», «Дата»…

Помогло решение из (13).Проблема оказалась со справочником банковские счета, было 2 элемента с не заполненными номерами счетов.

This question follows the answer of my other question there:
Insert with OUTPUT correlated to sub query table

The Person.LastName column has a NOT NULL constraint. When I execute this code:

CREATE TABLE tempIDs
( PersonId INT, 
  FinancialInstitutionId INT
);

MERGE INTO Person 
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
  VALUES (GetDate(), 'Legal', fi.Name, fi.Street, fi.Number, fi.City, fi.[State], fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
OUTPUT inserted.Id, fi.Id INTO tempIDs;

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

I get the following error:

Cannot insert the value NULL into column 'LastName', table 'Person'; column does not allow nulls. UPDATE fails.

The thing is that there is no FinancialInstitution.Name that is NULL.

SELECT Name FROM FinancialInstitution WHERE Name = NULL

This returns no row. Beside, if I replace fi.Name with a value (‘A Last Name’) the request works.