sql-serverselect-into

SELECT INTO a null literal of a specific type


How do I SELECT INTO a NULL value of a particular type?

For example, backing up data selectively and incorporating a DateFixed column that will start NULL and later be populated via SysUtcDateTime():

SELECT
    Thing.Id,
    Thing.SomeValue AS [BadValue],
    NULL AS [DateFixed]
INTO ThingValueFixBackup
FROM Thing
WHERE SomeValue IS WRONG SOMEHOW;

Solution

  • GETUTCDATE returns a datetime

    So you can use

    CAST(NULL AS DATETIME) AS DateFixed
    

    to get a column of that type created with your SELECT ... INTO.

    But you should consider using datetime2/SYSUTCDATETIME instead.