SQL Excel import to SQL Server table

--1, data table already created, Excel first row as table header

--Enable AD Hoc distributed Queries:

exec sp_configure ' show advanced options ', 1

Reconfigure

exec sp_configure ' Ad Hoc distributed Queries ', 1

Reconfigure

Go

--Start querying data

If object_id (' temp_xxx ') is not null

drop table temp_xxx Go

SELECT * into temp_xxx from OpenDataSource (' microsoft.ace.oledb.12.0 ', ' Data source=c:\users\administrator\appdata\ Local\temp\1\2012.xls; User id=; password=; Extended properties=excel 5.0 ') ... sheet1$

--Close ad Hoc distributed Queries after use is complete:

exec sp_configure ' Ad Hoc distributed Queries ', 0

Reconfigure

exec sp_configure ' show advanced options ', 0

Reconfigure

SELECT * from Temp_xxx;

SQL Excel import to SQL Server table