Cannot insert the value NULL into column'; column does not allow nulls. INSERT fails. (T-SQL)

Question

I have the following code:

set identity_insert CapModel.LedgerAmountByECMAccountByRptLOB ON

INSERT INTO [AnalyticsV2P7].[CapModel].[LedgerAmountByECMAccountByRptLOB] (AccountintDate,Ledger,Period,RptLOB,ECMAccount,Amount,AmountId)
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\Database\Data\AnalyticsV2P7\LedgerAmountByECMAccountByRptLOB.xlsx', 'SELECT * FROM [Sheet1$]');

set identity_insert CapModel.LedgerAmountByECMAccountByRptLOB Off

where AmountID is an identity column where NULL values are NOT allowed. In my Excel file, that column is blank (besides the header)

I'm getting the following error message:

Cannot insert the value NULL into column 'AmountId', table 'AnalyticsV2P7.CapModel.LedgerAmountByECMAccountByRptLOB'; column does not allow nulls. INSERT fails.

I understand that no NULL values are allowed because I set it up that way but isn't it supposed to fill the identity column so there are no NULL values?

I would appreciate any help!

Thanks

zimdanen · Accepted Answer · 2012-10-05 20:29:35Z

1

SET IDENTITY_INSERT means that you are providing the value for the identity column - which you are, as it's both in your columns list and being returned from your SELECT statement.

Remove the SET IDENTITY_INSERT lines, and remove the AmountId from the columns list. You will also need to enumerate the columns coming back from your Excel sheet so as not to provide the AmountId.

edited Oct 5, 2012 at 20:29

answered Oct 5, 2012 at 20:22

zimdanen

5,6367 gold badges47 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kristina Over a year ago

Thanks! I just looked into identity insert and i'm not too familiar with it.

Collectives™ on Stack Overflow

Cannot insert the value NULL into column'; column does not allow nulls. INSERT fails. (T-SQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related