SQL Query not throwing error even if the entered scale value is higher than specified
The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big.

You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation:


CAST and CONVERT (Transact-SQL)


I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea is to load the data into a table with say numeric(7,4). You can then check which values are truncated by a query similar to:

select t1.&lt;key&gt; 
from t1 join t2 
    on t1.&lt;key&gt; = t2.&lt;key&gt; 
where t2.&lt;col&gt; - t1.&lt;col&gt; &lt;&gt; 0