Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ziegler
Starting Member
3 Posts |
 Posted - 2014-10-16 : 17:21:47
|
| I have a csv/text file I am trying to BCP into SQL.Import file has 100376300014869616.00000 that goes into a float(8,53) and I get 100376300014869620.00000? This is happening to 782 rows out of 22,996,825 rows but it is correctly rounding up or down. Any ideas?Command Line:C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe {DB}..{table} in {file}.csv.txt -c -t}# -r?#END_LINE\n -k -b 500000 -S{server} -T -h "TABLOCK" -CACP |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-10-17 : 01:54:16
|
| Float is an approximate number data type. Also, conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero. This is what you are seeing.http://msdn.microsoft.com/en-us/library/ms173773.aspx |
 |
|
|
ziegler
Starting Member
3 Posts |
Posted - 2014-11-14 : 13:34:56
|
| So what is the work around? As [numeric](32,5) does the same thing? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ziegler
Starting Member
3 Posts |
Posted - 2014-11-17 : 09:15:11
|
| But [numeric](32,5) is still doing the same thing? The only thing I can think to try in BCP in to varchar then switch it to numeric. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-11-17 : 12:11:00
|
| Numeric should not be rounding your data. Could you post the following so we can test on our machines:1. Table definition2. Bcp command (you posted a command, but I think you must have some typos or I'm missing something: -t}# -r?#END_LINE\n)3. Sample bcp file, just 5-10 rows (a few without the issue and a few with the issue)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Sql 2008 R2 BCP in rounding float(8,53)?