无法将值 NULL 插入到使用 Sql Select 语句的列中 [英] Cannot insert the value NULL into column where using Sql Select statement

问题描述

你能告诉我如何避免下面提到的异常吗?

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);

异常:

无法将 NULL 值插入到列 'IsDeleted'、表 'Migrated.dbo.Cities' 中；列不允许空值.插入失败.

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

推荐答案

正如 @jamieD77 评论的那样，您在插入语句中缺少 IsDeleted 列.

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

该错误意味着该列被标记为NOT NULL"，因此在创建新行时必须插入一个值.

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

因此您要么需要自己从表中删除 NULL 约束，要么在列中插入一个值.

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);

对于一个 bit 字段，我认为它是(但你应该确认！)值 0 将是 false 和 1 将是 true.如果字段是不同的数据类型，这些值可能有不同的含义！！！

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!!!

这篇关于无法将值 NULL 插入到使用 Sql Select 语句的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！