Cannot insert the value NULL into column where using Sql Select statement

Question

Can you tell me how to avoid below mentioned exception ?

 INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode)
     SELECT DISTINCT 
         a.City, a.ZipCode 
     FROM
         [Legacy].[dbo].[Ziplist] AS a 
     WHERE
         (a.City IS NOT NULL AND a.ZipCode IS NOT NULL);

Exception:

Cannot insert the value NULL into column 'IsDeleted', table 'Migrated.dbo.Cities'; column does not allow nulls. INSERT fails.

add IsDeleted to your insert statement, and give it a value.. OR add a default constraint to the field with a default value OR make it nullable — JamieD77, Jun 16 '16 at 19:17
@JamieD77 Then how to fill it ? I need to put `false` on it. — Sampath, Jun 16 '16 at 19:18
`INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode, IsDeleted) select DISTINCT a.City,a.ZipCode, 0 from ...` assuming it's a bit column — JamieD77, Jun 16 '16 at 19:19
Possible duplicate of [SQL Server : "cannot insert the value NULL into column" but column is not null](http://stackoverflow.com/questions/14565842/sql-server-cannot-insert-the-value-null-into-column-but-column-is-not-null) — Andrew Morton, Jun 16 '16 at 19:22
Possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — Tab Alleman, Jun 16 '16 at 19:22

Matt · Accepted Answer · 2016-08-10T18:08:39.773

7

As @jamieD77 commented you are missing the IsDeleted column in your insert statement.

The error means that the column is marked as "NOT NULL" and therefore a value must be inserted when a new row is created.

So you either need to remove the NULL constraint from the table yourself or insert a value in the column.

 INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode, IsDeleted)
 select  DISTINCT a.City,a.ZipCode,0 from [Legacy].[dbo].[Ziplist] as a where ( 
 a.City is not null and a.ZipCode is not null);

For a bit field which I would assume it is (but you should confirm!) the value 0 would be false and 1 would be true. If the field is a different data type these values may have different meanings!!!

edited Aug 10 '16 at 18:08

answered Jun 16 '16 at 19:20

Matt

13,833
2
16
28

Can you tell me how to insert a value ? I need to insert `false` there. – Sampath Jun 16 '16 at 19:22
if binary you would insert a 0, please confirm for example I may use a tinyint in certain cases to allow for more meanings than just deleted or not deleted. There could be a status for mark for review prior to delete etc. – Matt Jun 16 '16 at 19:24
Yes,That is fine.Column type is `bit`.It's working.Thanks a lot :) – Sampath Jun 16 '16 at 19:31

score 0 · Answer 2 · answered Jun 16 '16 at 19:22

What the error message says is that the IsDeleted column is declared NOT NULL and it does not have a default value. In this case, you are probably inserting non-deleted records by default, so you might want to change column:

alter table cities alter column IsDeleted int not null default 0;

Alternatively, you can write the query to include the column:

INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode, IsDeleted)
    select  DISTINCT zl.City, zl.ZipCode, 
    from [Legacy].[dbo].[Ziplist] zl
    where a.City is not null and a.ZipCode is not null;

These answers assume that IsDeleted is an integer with "0" for false. If the values are stored differently, then the code needs to be modified appropriately.

score 0 · Answer 3 · answered Jun 16 '16 at 19:25

Simply add the column to insert list and fixed value to select list.

INSERT INTO [Migrated].[dbo].[Cities] (Name, ZipCode, IsDeleted)
select  DISTINCT a.City,a.ZipCode, 0 IsDeleted --0 means false
  from [Legacy].[dbo].[Ziplist] as a 
    where (a.City is not null and a.ZipCode is not null);

Cannot insert the value NULL into column where using Sql Select statement

3 Answers3

Linked

Related