March 9, 2009 at 12:45 pm
I am in the process of upgrading several databases from SQL 2000 to SQL 2005. From my testing, it appears that when inserting data that contains more decimal places than a column can hold, that data is being truncated in SQL 2000 and rounded in SQL 2005. Is there a setting in SQL 2005 that will cause the data to be truncated, like it was in SQL 2000?
For example I have a column defined as:
columnName DECIMAL(5,2)
And when I try to load a value of 3.456 into that column, 3.45 will be entered into the SQL 2000 table and 3.46 will be entered into the SQL 2005 table.
March 9, 2009 at 2:57 pm
Use the round function to explicitly control this behavior.
declare @Num decimal(6,3)
set @Num = 3.289
select round(@Num, 2, 1)
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 9, 2009 at 3:04 pm
Thank you for the response, but I was wanting to know if there is a setting that can be applied at the Database or Server level to accomplish this. At this point in the migration, truncating the data programmatically is not an option.
March 9, 2009 at 3:19 pm
Not to my knowledge...
Just curious why you can't do the explicit conversion using SQL? There may be another way to skin this cat...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 9, 2009 at 3:41 pm
The application that goes along with the database inputs data from .txt files into numerous (~100) tables via the 'bulk insert' command. There is a one-to-one relationship between the .txt files and the tables. The .txt files may contain 4 decimals when we are expecting 3. The new data is merged with existing data and if data in an entry was rounded up, then a duplicate record is inserted and it that is the root of the problem.
March 9, 2009 at 3:45 pm
I'm sure this is more work than you want, but you could create staging tables for those tables (hopefully not the majority of them) and load into the staging tables with the precision of 4, then use a query to migrate the data from the staging tables into the final destinations.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply