SQL Server: How can I copy data from one table to another table without the characters 'e+' being added to the results

By : anpatel
Source: Stackoverflow.com
Question!

I am copying data from a table created by an Excel spreadsheet import.

Here is what I am doing to move the data from the import table to the actual table:

INSERT INTO TableName ([Column1], [Column2])
SELECT [Column1], [Column2] FROM [Sheet1$] 

After I execute this under column 1 I get records like:

5.31752e+007 when the actual value of that record (from the table I am importing from) is 5046610163

Any ideas?

By : anpatel


Answers
You didn't specify whether it was Column1 or Column2 that has the float data in it, but here's an example assuming Column1 is what you need.

INSERT INTO TableName ([Column1], [Column2])
SELECT CAST([Column1] AS decimal(38,2)), [Column2] FROM [Sheet1$] 

I use the same code to convert to varchar without all that formatting quite often:

SELECT CONVERT(varchar(100), CAST(@value as decimal(38,2)))


EDIT

OK -- then try to first open your excel sheet, create a 3rd column, and make it a formula (=UPPER(A1)), where A1 is actually whatever column you're having the trouble with.

Beyond that, if you search for "scientific notation" in Excel help, you'll get some limited advice there as well.

EDIT 2

Or try this in TSQL:

INSERT INTO TableName ([Column1], [Column2])
select LTRIM(str(cast([Column1] as real))), [Column2]
FROM [Sheet1$] 
By : Chains


This video can help you solving your question :)
By: admin