Inserting missing date into non nullable Datetime field-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.