Home SQL Server Create table to autoconvert int -> decimal
Reply: 0

SQL Server Create table to autoconvert int -> decimal

user1107
1#
user1107 Published in June 22, 2018, 11:32 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31325 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO