In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to?
Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED


Assuming ID is where you're getting row 1 or row 2 value.