System conversions always pose challenges.  Mapping fields from the old system to the new system is challenging enough even before you factor in the necessary data type conversions.  It seems every “old” system has repurposed fields.  You know what I mean.  That numeric value stored in that text field means you now have to code around non-numeric values in your conversion logic.

SQL Server’s ISNUMERIC() function helps that effort.  ISNUMERIC() accepts a text string as an input and returns a Boolean result depending on whether or not SQL Server believes the input text string could be converted to a numeric value.  SELECT ISNUMERIC('1' ) returns TRUE.  SELECT ISNUMERIC('A') returns FALSE.  The logic seems simple enough.  One example of handling non-numeric values so your data conversion does not fail includes:

DECLARE @NumericString VARCHAR(10)
SET @NumericString = 'A'

Recently, while working with T-SQL's merge statement and its output clause I ran across an error I couldn't track down.  My statement looked something like this:

Merge #target_1 as target using 
(select value1 from #source ) as source 
on target.value1 = source.value1 
when not matched then insert (value1) values (value1) 
OUTPUT $action, inserted.* into #action_target;

The code was producing this error

Msg 213, Level 16, State 1, Line 25
Column name or number of supplied values does not match table definition.