Changing a Table's data type in code.

After I Imported a file from Excel, all the number fields are float.
I know I can change the Mappings when I import, but I don't know if that is going to corrupt the data or not.

Can I update the Table's field types in a stored procedure?
I want to change a few to money and int.
Also some of the fields are like : 54.232312121
How can I make them 2 decimal places like : 54.23 ?  thanks

LVL 16
MikeMCSDAsked:
Who is Participating?
 
Patrick MatthewsCommented:
To always round up:

UPDATE SomeTable
SET SomeColumn = ROUND(SomeColumn + 0.0049, 2)

That will still store 4 decimal places, but the final two will be zeroes.
0
 
Patrick MatthewsCommented:
MikeMCSD,

If you are concerned that some of your Excel data will be "messy" (e.g., what's supposed to be a numeric field may contain text every so often), then the safest thing to do would be to load to a staging table first in which ALL of the columns use varchar or nvarchar.  You can then run some validation procedures against the staging data before transferring the data into your permanent tables.

Patrick
0
 
MikeMCSDAuthor Commented:
>> safest thing to do would be to load to a staging table first  . .
Using the Import Wizard or something else? Isn't that what I'm doing now?

All the numeric  fields are ok after the import, they are just floats.
I need to update other fields (that have money and int data types) in another Table
from this Imported table that has the floats.
And also, this is going to be a weekly thing I have to run.


0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Patrick MatthewsCommented:
If you have no concerns about whether your data need to be validated, then simply fix your import process to use the specific data types that you want, rather than letting them default to float.
0
 
MikeMCSDAuthor Commented:
Yeah I guess . . . . but this Import Wizard seems to have a mind of its own sometimes.
I did try that and it did work, but the money fields still displayed 45.15151215115.
Is there a way to fix that with an Update statement?





0
 
Patrick MatthewsCommented:
Better check that again.  Since the money data type's maximum decimal precision is four places, I doubt that you are actually using that data type if your value is showing as 45.15151215115

:)
0
 
MikeMCSDAuthor Commented:
Yes, not like that, but like this : 1.5875
How do I get it to 2 places, rounded up?
0
 
MikeMCSDAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.