insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable
Excel 2007 (.Xlsx) 文件:
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable
二. 从 Excel 导入数据到 SQL Server 新表
Excel 2003 (.Xls) 文件:
select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) 文件:
Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
三. 从 Excel 导入数据到 SQL Server 已存在的表
Excel 2003 (.Xls) 文件:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) 文件:
INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
四.从 Excel 导入数据到 SQL Server 新表 (使用动态的表名 如SQLTABLE_200110413) (http://www.office-cn.net)
DECLARE @table varchar(500)
DECLARE @Q varchar(2000 )
SET @table='SQLTABLE_' +(CONVERT(VARCHAR(8),GETDATE(),112))
SET @Q= 'select * into '+ @table + ' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
