EXCEL导入数据到临时表newtable的时候 数值数据是正常的!和EXCEL的数据一致,为什么从newtable查询数据插入到指定的数据库指定的表的时候,数据会被科学计算法格式化类似这种1.33025e+010 都变成这种,导入数据的时候EXCEL都是文本的,newtable的数据完全没问题,就是执行插入后,才出这问题,困扰得我很厉害。 求在线能帮忙解决
原始数据导入到newtable的时候数据是这样的 select * into newtable from
OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\lunxun\20100504.xls')...[_TempCirpatReport$] 结果:
13302476360
13302476359
15338804925
15338804925
15338814647
15338814647
15338814002
15338814364
15338814364数据是健康无错的!
执行插入SQL语句后:insert into RotationInfo(RotationResult,RotationResultCash,RotationCountFix,RotationBacktime,RotationId,
RotationInfoName,RotationStyle,RotationArea,
RotationAddress,RotationGrade,RotationCross,RotationStatus,RotationToAddress,RotationTelPhone,RotationSearchTelPhone,
RotationMobile,RotationCardId,RotationLongitude,Rotationlititude,RotationImportant,RotationFactory,
RotationSuppliers,RotationConstruction,RotationBlog)
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,
F19,F20,F21,F22,F23,F24 from newtable
数据如下:
1.33025e+010
1.33025e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.33025e+010
1.33025e+010
1.53388e+010执行过程中没任何问题,没想到现在成这样 确实很困扰
原始数据导入到newtable的时候数据是这样的 select * into newtable from
OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\lunxun\20100504.xls')...[_TempCirpatReport$] 结果:
13302476360
13302476359
15338804925
15338804925
15338814647
15338814647
15338814002
15338814364
15338814364数据是健康无错的!
执行插入SQL语句后:insert into RotationInfo(RotationResult,RotationResultCash,RotationCountFix,RotationBacktime,RotationId,
RotationInfoName,RotationStyle,RotationArea,
RotationAddress,RotationGrade,RotationCross,RotationStatus,RotationToAddress,RotationTelPhone,RotationSearchTelPhone,
RotationMobile,RotationCardId,RotationLongitude,Rotationlititude,RotationImportant,RotationFactory,
RotationSuppliers,RotationConstruction,RotationBlog)
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,
F19,F20,F21,F22,F23,F24 from newtable
数据如下:
1.33025e+010
1.33025e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.53388e+010
1.33025e+010
1.33025e+010
1.53388e+010执行过程中没任何问题,没想到现在成这样 确实很困扰
cast(cast(col as sql_variant) as varchar(20))
在从 newtable 表向 RotationInfo 表插入数据时使用 str 函数将 float 类型转换为字符类型。
例如,if OBJECT_ID('tempdb..#1') is not null
drop table #1;
go
create table #1 (num float);
go
insert into #1 values(13302476360);
goif OBJECT_ID('tempdb..#2') is not null
drop table #2;
go
create table #2 (num varchar(30));
goinsert into #2 select num from #1;
insert into #2 select STR(num,11) from #1;
select * from #2;
/*
1.33025e+010
13302476360
*/
那我想CONVERT(varchar,F14)是可以转换的吧>但是就是有问题啊!
貌似你看不到代码。NEWTABLE是类似的临时表,没有数据类型!
drop table #1;
go
create table #1 (num float);
go
insert into #1 values(13302476360);
gouse tempdb
go
sp_columns '#1'
/*
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
tempdb dbo #1__________________________________________________________________________________________________________________00000000003A num 6 float 15 8 NULL 10 1 NULL NULL 6 NULL NULL 1 YES 109
*/