Truncation of a float field when copying

Question

Truncation of a float field when copying

scoleman_cambridge

SSC Enthusiast

Points: 180
More actions
July 14, 2011 at 8:43 am

#254556

I am attempting to copy data from one table to another. The source table contains float data:
Example: Longitude -80.528815, Latitude 43.48910333
When I try to copy the data from the source table, it truncates the number at the decimal point at the destination (the minus sign is kept):
Example: Longitude -80., Latitude 43.
I have tried a string conversion from float. I even a query where the string conversion changed "." to "d" and "-" to "m" in attempt to force SQL to treat as an alphanumeric string rather than a number. Any ideas?
SQL Version: Microsoft SQL Server 2005 x64 Enterprise Edition SP3 (9.0.4053)
O/S: Microsoft Windows Server 2003 Standard x64 Edition SP2
Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

what's the datatype of the destination table?

if the destination is INT or DECIMAL(10,0) you would loose everything to the right of the decimal point due to implicit conversions.

the minimum size would probably be DECIMAL(10,8) (10 significant digits, 8 decimal places)

and i would suggest using decimal(12,8), since there are some longitudes that are like 117.12345678

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scoleman_cambridge SSC Enthusiast Points: 180 More actions · Answer 2

The destination data types are also float. All the longitudes and latitudes are limited to the -80 long and 43 lat range (the table records bus stops for a transit system, so all the coordinates are in the same area). I tried the decimal(10,6) and I received -80.000000 and 43.000000 for all longitudes and latitudes! It truncated and then added zeroes...

If I had any hair left I would be losing it!

Lowell SSC Guru Points: 323547 More actions · Answer 3

well good we eliminated that as being a possible issue.

how is the data being migrated from source to destination?

Somewhere in that process, it seems, would be the issue.

is it a linked server/TSQL statement? an application, maybe using declared parameters? an SSIS package?

that's the next place to look, I'm sure we can track this down.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scoleman_cambridge SSC Enthusiast Points: 180 More actions · Answer 4

I can manage to do a straight import from 1 dbs to another dbs without truncating data, but if I try to copy the data to another table in the same dbs I get truncation. Also, if I apply parameters to the import I get truncation - example from 2011-02-02 to 2011-05-25. BTW - the full table currently has about 32,929,409 records.

Lowell SSC Guru Points: 323547 More actions · Answer 5

scoleman_cambridge (7/14/2011)
I can manage to do a straight import from 1 dbs to another dbs without truncating data, but if I try to copy the data to another table in the same dbs I get truncation. Also, if I apply parameters to the import I get truncation - example from 2011-02-02 to 2011-05-25. BTW - the full table currently has about 32,929,409 records.

how are you doing the import?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scoleman_cambridge SSC Enthusiast Points: 180 More actions · Answer 6

The import is done using the import task in SSMS. I've also saved it as a dtsx file.

kramaswamy SSCoach Points: 18181 More actions · Answer 7

I'm betting that if you looked into the integration services task, you would find that the OLE DB Source Input Data is importing the data in one format, and then the Output Data is converting it into a different format. Either that or, the OLE DB Destination is doing one of those two things.