sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

1、查询Excel数据
--office 2007
SELECT *
FROM openrowset('MICROSOFT.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;DATABASE=d:\基金（abc）.xlsx','select * from [基金$]')
-----------------------------------
--office 2003
SELECT *
FROM openrowset('MICROSOFT.JET.OLEDB.4.0',
'Excel 8.0;HDR=YES;DATABASE=d:\基金（abc）.xls','select * from [基金$]')
-----------------------------------
--office 2000
SELECT *
FROM openrowset('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=d:\基金（abc）.xls','select * from [基金$]')
------------------------------------------

SELECT *
FROM openrowset('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=c:\aa.xls','select * from [sheet1$]')

GO

SELECT *

FROM　OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\aa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[sheet1$]
GO

2、查询到的数据导成一个Excel文件
A．BCP

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

参数：S 是SQL服务器名；U是用户；P是密码

3、往Excel插入数据:

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

ITPub博客

有关SQL SERVER 操作excel

最新文章