Cannot insert the value NULL into column 'ID'

Hi dear experts:

The message is

"Cannot insert the value NULL into column 'ID_Month', table 'Database_DW.dbo.Incident_Summary'; column does not allow nulls..."

At my table, I have a primary key which make autonumber for any record set in column 'ID_Month'. How can I insert autonumber in column 'ID_Month'by SQL statement?

Thank you in advance.

It's possible you are explictly inserting null into the column rather than ignoring it. Check all column mappings and sql statements to ensure you are not referencing that column. For example:

insert Database_DW.dbo.Incident_Summary(ID_Month, column1) select(NULL,somevalue) from sometable; would return that error.

|||

Hi Michanne,

thank you for the quick respond. Yes, you are wright, I am inserting nulls in the column that does not allow nulls.

My question is how to insert next number in the column by sql statement. I copied the d/base with data, 57 records exists in it and I don't know how to insert the record that will be number 58.

Thanks.

|||

Leave it empty in your statement. Don't reference in your column list either. It will insert it automatically as long as 58 is the next identity value. (SELECT IDENT_SEED('table') will tell you this.) I think that is what you are getting at....

There are also functions that allow you to override the identity key value but you only need to do that if you care about the value or have to correct the value in the table. To do that look up the syntax for "Set Identity insert" in BOL.

Hope that helps!

|||

Thanks for following michanne!

I can't leave it. In this case I receive a message "Cannot insert the value NULL into column 'ID_Month'. It is something with identity syntax. I can't figure out.

Thank you!

|||

Check the schema. If it is >truly< an identity it will work to insert all other columns and ignore the one that is an identity.

|||

Once again, thank you!! Checked the schema, no identity, it is float with no null values.

Your suggestion was so helpful!!