how to validate data before we do data mapping or migration
I have task where i need to move data from one table1 to table2, i have source columns and destination columns but before i move data is it possible to find out is there any data in source column doesn't not fit destination column because of destination field type difference or field length is less like that ? Like in a scenario i have source column which is char(1) and i am trying to map that column data to int column, here most of rows in source column have integer values but there are some alpha characters in some rows so i want to find out all those rows which are going to fail....the logic should work for all data types. Thanks in advance!!
Make a temporary copy of table2, attempt to move the data from table1, catch all the defects, code around them, repeat. Or.... Use TRY...CATCH, attempt to move the data into table2 (or copy of), putting any 'failing' rows into a copy of the table that has a column type to hold most data e.g. varchar(max)