I'm not sure if this is possible but here goes.
I have financial data stored in a csv format. The data unfortunately lacks any decimal points in the dollar fields. so $100.00 is stored as '00000010000'. Its also stored as a string. In my current setup I upload the csv file into a staging table with all columns set to varchar(x).
I know that if I try to insert this value into an integer column it will automatically convert it to 10000 of type integer, but that means I am missing my decimal place.
Is there anyway I can create a table such that inserting an integer stored as a string or integer automatically converts it to an decimal with 2 places behind the decimal????
EX: '000010000' -> 100.00
I know I can cast the column to a decimal and divide the existing value by 100.... but this table has 100+ columns with 60+ of them needing to be recast. This is also only table 1 of 6. I want to avoid creating commands to individually changing the relevant columns. Not all columns containing a number need the decimal treatment.