Solved

Changing a Table's data type in code.

Posted on 2011-09-25
8
215 Views
Last Modified: 2012-05-12
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
Comment
Question by:MikeMCSD
  • 4
  • 4
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36595686
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36595753
>> 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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36595758
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36595842
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36595849
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36595865
Yes, not like that, but like this : 1.5875
How do I get it to 2 places, rounded up?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36596038
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
 
LVL 16

Author Closing Comment

by:MikeMCSD
ID: 36597087
thanks
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now