无法将值 NULL 插入到“创建时间”列中

Question

运行时异常：

无法将值 NULL 插入到“创建时间”列、表 “我的表”中;列不允许空值。插入失败。

代码:

INSERT INTO [MyTables] (LegacyId, CreationTime)
    SELECT DISTINCT 
        a.[IPLID], a.[inputdate] 
    FROM 
        [Legacy].[dbo].[MyTables2] AS a 

请问当< code>a.[inputdate]为< code>Null时，如何插入像< code>01/01/2000这样的自定义日期？

Answer 1

使用ISNULL函数。

INSERT INTO [MyTables] (LegacyId,CreationTime)
SELECT DISTINCT a.[IPLID],ISNULL(a.[inputdate], '01/01/2000') FROM [Legacy].[dbo].[MyTables2] as a 

Answer 2

只需用一个< code>ISNULL括起来:

INSERT INTO [MyTables] (LegacyId,CreationTime)
SELECT DISTINCT a.[IPLID],ISNULL(a.[inputdate], '01/01/2000')
FROM [Legacy].[dbo].[MyTables2] as a