• 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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