Sql 操作 Execl

--開啟sql server 操作Execl服務

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

------操作例項-----------------------------

select * from OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="D:\table.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1]
(
 TableName,
 FieldName
)

 

insert into OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="D:\table.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
(
 TableName,
 FieldName
)

select a.[name],b.[name]
 from syscolumns as a --in (select id from sysobjects where xtype = 'U' And [Name] = 'Products')

left join dbo.systypes AS b ON a.xtype = b.xusertype

where a.id =object_id('Products')

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17140602/viewspace-582327/，如需轉載，請註明出處，否則將追究法律責任。