Inserting missing date into non nullable Datetime field

Question

Here we have an existing database and I'm building a new system with a new database.

There I need to transfer some data from the old database table to the new database table.

I wrote this query in the SQL

INSERT INTO [Mondo-UAT].[dbo].[Countries] (
    [Country_Name]
    ,[Country_Code]
     ,[Note]
     
    )
SELECT [Code1]
    ,[Code3]
    ,[Name]
    
FROM  [MondoErp-UAT].[dbo].[Nations]

The issue is in the [Mondo-UAT].[dbo].[Countries] table has other columns like Note is a string and Status is a bool CreateBy is int CreateDate is DateTime . So when I run the query it returns with an error

Msg 515, Level 16, State 2, Line 8 Cannot insert the value NULL into column 'CreatedDate', table 'Mondo-UAT.dbo.Countries'; column does not allow nulls. INSERT fails. The statement has been terminated

So I wanna know how to insert data for the CreateDate,CreateBy ,Notes from the above script I wrote.

The error is pretty clear and has nothing to do with multiple databases. That other table requires a value for that field. You'll have to either change the field to allow NULL or come up with a default when the source value is NULL. Would the current date make sense? Or '0001-01-01` ? — Panagiotis Kanavos, Sep 20 '22 at 08:26
Note that tables have columns, not fields. (And a date consists of a year field, a month field and a day field.) — jarlh, Sep 20 '22 at 08:30
@jarlh No I want to know that for the ``CreateDate`` column asking for a value know. So how to pass a value to that column in the script I wrote. — Dev Beginner, Sep 20 '22 at 08:36
@jarlh that's nitpicking and actually wrong. Dates in SQL Server are single binary values. If you try to decode them, all use various forms of an offset since an epoch and time component. No year, month or day fields — Panagiotis Kanavos, Sep 20 '22 at 08:36
@PanagiotisKanavos Hi guys. I know why the error is triggering. :) I want to know how to pass a default value to the ``CreateDate`` as DateTime.Now() , ``CreateBy`` column as 1 from the above query. : ) — Dev Beginner, Sep 20 '22 at 08:39
@PanagiotisKanavos, I was not talking about physical storage format, they are all binary in the end I suppose, I was just mentioning that a date has year, month and day _fields_ - according to the SQL specification. E.g. today's date has the year _field_ value 2022. — jarlh, Sep 20 '22 at 08:52

score 1 · Accepted Answer · answered Sep 20 '22 at 08:40

If the target table has non-nullable columns without defaults, you have to specify values for those fields when inserting data.

The easiest solution would be to modify the target table to add DEFAULT values for those fields, eg SYSDATETIME() for Created, SYSTEM_USER for CreatedBy and '' for Notes. For Status you'll have to decide what a good default status is. 0 may or may not be meaningful.

Otherwise, these values will have to be specified in the query:

INSERT INTO [Mondo-UAT].[dbo].[Countries] (
    [Country_Name]
    ,[Country_Code]
    ,[Note]
    , Created
    , CreatedBy
    , Status
    )
SELECT [Code1]
    ,[Code3]
    ,[Name]
    , SYSDATETIME()
    , SYSTEM_USER
    , 0
FROM  [MondoErp-UAT].[dbo].[Nations]

SYSTEM_USER returns the login name of the current user, whether it's a Windows or SQL Server login. This makes it a good default for user columns.

SYSDATETIME returns the current time as a DATETIME2(7) value. If Created is a DATETIMEOFFSET, SYSDATETIMEOFFSET should be used.

Thank you so much. It is a simple one, The way I think was wrong,. Thank you again for the example. :) — Dev Beginner, Sep 20 '22 at 08:46

score 0 · Answer 2 · answered Sep 20 '22 at 08:29

0

You can set Default Value for CreateDate,CreateBy,Notes columns in the [Mondo-UAT].[dbo].[Countries] table if they are not null columns. So, When you insert data into the table and do not insert a value for these columns, the default value will be inserted.

answered Sep 20 '22 at 08:29

Ehsan HP

456
2
5
16

Could you show me an example of how to pass the value to the **CreateDate** I Tried in the ``Insert`` section by adding ``CreateDate`` and ``= DateTime.now()`` but can't add that way right? – Dev Beginner Sep 20 '22 at 08:34
yes, we can add default values by wizard or script : ALTER TABLE YourTable ADD CONSTRAINT DF_DateTime DEFAULT GETDATE() FOR YourColumn – Ehsan HP Sep 20 '22 at 08:43

Inserting missing date into non nullable Datetime field

2 Answers2