SQL SERVER导入、导出数据到Excel

SQL Server 2005 使用SQL命令开启opendatasource,openrowset的办法 

--show advanced options
sp_configure 'show advanced options',1
go

--Enable distributed query/transaction in AD HOC query like opendatasource/operquery
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
Go

EXCEL文件工作表是默认的Sheet命名为Sheet1,对应的字段的名称(第一行为标题)为SN

---向Excel查询
select * from opendatasource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=D:\SN.xls')[sheet1$]
where SN='SW88380E31G0'

select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\SN.xls;','select * from [sheet1$]') 
where SN='SW88380E31G0'

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=2;DATABASE=D:\SN.xls',[sheet1$])
where SN='SW88380E31G0'

1)如遇OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)"报错,请安装SQL SP2补丁.

2)hdr=yes时可以把xls的第行作为字段看待，如不加HDR=yes;则默认为yes,如hdr=no的话，where时就会报错
3)[]和美圆$必须要，否则就会报错

 

---修改Execl
Update opendatasource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=D:\SN.xls')[sheet1$]
set SN='TESTESTESTES'
where SN='SW88380E31G0'

Update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\SN.xls;','select * from [SN$]')
set SN='TESTESTESTES'
where SN='SW88380E31G0'

Update OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=2;DATABASE=D:\SN.xls',[sheet1$])
set SN='TESTESTESTES'
where SN='SW88380E31G0'

 

---导出Excel
insert into opendatasource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=D:\SN.xls')[sheet1$](SN)
select 'INININININININ' As SN

insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\SN.xls;','select * from [SN$]')(SN)
select 'INININININININ' As SN

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=2;DATABASE=D:\SN.xls',[sheet1$])(SN)
select 'INININININININ' As SN

BTW,BCP方式:

EXEC master..xp_cmdshell '"Del D:\SN.xls"'
---导出Excel
Declare @iSQL varchar(2000)
Set @iSQL='bcp "Select Serial_Number from PAL.dbo.Tsestatu where Serial_Number=''SW88380E31G0'''
Set @iSQL=@iSQL+' order by Serial_Number" queryout D:\SN.xls -c -S"172.26.40.21" -U"sa" -P"sa"'
EXEC master..xp_cmdshell @iSQL

---Txt导入SQL
Create Table AAA_SN_BK070724 (SN varchar(30))
Declare @iSQL nvarchar(600)
Set @iSQL='bcp "PAL.dbo.[AAA_SN_BK070724]" In "D:\SN.txt" -c -S"172.26.40.21" -U"sa" -P"sa"'
EXEC master..xp_cmdshell @iSQL
Select * from AAA_SN_BK070724