• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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

0
MikeMCSD
Asked:
MikeMCSD
  • 4
  • 4
1 Solution
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now