Excel 2010导数据到SQL SERVER 2008

 

1、需要注意的是导入前需要打开sp_configure，执行以下代码即可

ALTER PROCEDURE [dbo].[Openconfigure]
AS
BEGIN
exec sp_configure 'show advanced options',1 
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1 
reconfigure
END

2、另外还需要将SQL Full-text Filter Daemon Launcher和MSSQLSERVER登录帐户改为LocalSyatem

3、最后执行下面sql语句

INSERT INTO TableTest(fName ,fSize ,fColor ,fBarcode)
SELECT F1,F2,F3,F4 FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;DATABASE=C:\furniture.xlsx',sheet1$)

大功告成

 

---下面是转别人的。

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\A.xls;User ID=admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]

 

select   *   from  
openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;HDR=YES;DATABASE=\C:\A.xls',[sheet1$])  

 

导入远端sql

insert OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=zxp;User ID=sa;Password='
         ).pubs.dbo.tablea(aa,bb,cc)
SELECT aaa,bbb,ccc
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\A.xls;User ID=admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]