SQLSERVER2005 ,EXCEL 导入导出
2008-08-20 10:08:24| 分类:MsSqlServer
| 标签:|字号大中小订阅
-----------------------启动关闭服务-----------------------------------
--启动Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure
go
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
go
reconfigure
go
exec sp_configure 'show advanced options',0
go
reconfigure
go
-----------------------------------------------------------------------
--查询excel 注:excel文件列名为:item1,item2,item3,item4
select * from openrowset('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\test.xls',[sheet1$])
select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\Test.xls','select * from [sheet1$]')
select * from opendatasource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\test.xls')...Sheet1$
--将excel数据导入到数据库
--导入数据并生成表
select * into userinfo from openrowset('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\test.xls',[sheet1$])
--如果接受数据导入的表已经存在
insert into userinfo select * from openrowset('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\test.xls',[sheet1$])
--将数据库数据导出到excel
--如果文件已经存在
insert into openrowset('MICROSOFT.JET.OLEDB.4.0','excel 8.0;HDR=YES;database=D:\test.xls',sheet1$)
select * from userinfo
--如果文件不存在
--EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
--参数:S 是SQL服务器名;U是用户;P是密码
--说明:还可以导出文本文件等多种格式
EXEC master..xp_cmdshell 'bcp test.dbo.userinfo out d:\test2.xls -c -q -S"127.0.0.1" -U"sa" -P"123"'
--插入数据到excel
insert into openrowset('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=d:\test.xls;','select * from [Sheet1$]')(item1,item2,item3,item4)
values ('11','22','33','44')
--修改excel中的数据
update openrowset('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=d:\test.xls;','select * from [Sheet1$]')
set item1='222222' where item4 like '%2%'
--结果查询
select * from userinfo
--删除表
drop table userinfo