If you have worked with SQL in the past you have likely seen this error when inserting data, it’s always a tricky one to investigate as the error message doesn’t actually say which column it is failing to insert on, so it can be a pain to troubleshoot, especially if the table has 50+ columns!
Consider this simple insert statement, the table coffee, contains a column named supplier that only allows a maximum of 10 characters. Our variable @Supplier is actually set to over 10 characters, so when inserting this fails, but it doesn’t tell us it fails on the column supplier.
In SQL Server 2019, it has the feature I have been waiting years for, it gives you detailed error logging on this error, providing the table name, column, and the value you are attempting to insert.
This new message is amazingly helpful when investigating this error as Developers, it will save us loads of time! It’s also worth noting on databases created on SQL Server 2019, the compatibility level is automatically set to 150, so this functionality is instantly available. On Databases created prior to SQL Server 2019, the compatibility level will likely be less than this so you will not have access to this functionality without increasing the compatibility level to 150 (SQL Server 2019).