INSERT INTO employee (name, officetel) SELECT 姓名, 手机号码 FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="F:\Book1.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0' )...[Sheet1$] Rowset_1 这是我写的代码,要怎样改?
select 姓名,cast(手机号码 as varchar) as 手机号码试试看,我自己没试。。
在EXCEL里面设置为文本,估计是这个问题,先尝试下
INSERT INTO employee (name, officetel) SELECT 姓名, 手机号码=cast(手机号码 as bigint) FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="F:\Book1.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1
--因为employee.officetel是varchar类型,通过OpenDataSource读出的手机号码是float。 --老乌龟说:“在excel里面选中字段,设置为文本即可.”这个其实解决不了问题,因为即使这样通过OpenDataSource读出的仍然是float。 --float转换成varchar,会变成科学记数字串,虽然你的语句没有显式转换,但SQL会进行隐式转换:declare @Phone float set @Phone = 13988776655 select float = @Phone, varchar = cast(@Phone as varchar)/* 13988776655 1.39888e+010 */--先转成bigint就正确了 select float = @Phone, varchar = cast(cast(@Phone as bigint) as varchar) /* 13988776655 13988776655 */--所以: INSERT INTO employee (name, officetel) SELECT 姓名, 手机号码=cast(手机号码 as bigint)-->转为bigint再让SQL隐式转换为varchar FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="F:\Book1.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1--或者 INSERT INTO employee (name, officetel) SELECT 姓名, 手机号码=cast(cast(手机号码 as bigint) as varchar)-->转为bigint再转换为varchar FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="F:\Book1.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1
(name, officetel)
SELECT 姓名, 手机号码
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Book1.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0'
)...[Sheet1$] Rowset_1
这是我写的代码,要怎样改?
select 姓名,cast(手机号码 as varchar) as 手机号码试试看,我自己没试。。
(name, officetel)
SELECT 姓名, 手机号码=cast(手机号码 as bigint)
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Book1.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1
--老乌龟说:“在excel里面选中字段,设置为文本即可.”这个其实解决不了问题,因为即使这样通过OpenDataSource读出的仍然是float。
--float转换成varchar,会变成科学记数字串,虽然你的语句没有显式转换,但SQL会进行隐式转换:declare @Phone float
set @Phone = 13988776655
select float = @Phone, varchar = cast(@Phone as varchar)/*
13988776655 1.39888e+010
*/--先转成bigint就正确了
select float = @Phone, varchar = cast(cast(@Phone as bigint) as varchar)
/*
13988776655 13988776655
*/--所以:
INSERT INTO employee
(name, officetel)
SELECT 姓名, 手机号码=cast(手机号码 as bigint)-->转为bigint再让SQL隐式转换为varchar
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Book1.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1--或者
INSERT INTO employee
(name, officetel)
SELECT 姓名, 手机号码=cast(cast(手机号码 as bigint) as varchar)-->转为bigint再转换为varchar
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Book1.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] Rowset_1