Excel导入SQL SERVER中

来源：互联网发布：微商城免费源码大全编辑：程序博客网时间：2024/05/18 15:05

--Excel导入SQL SERVER中

--表结构不存在可以使用
--启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

select * into serv_user_bak1 from
OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:/test.xls;','select * from [Sheet1$]')

---使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
-----------------------------------------------------------

--表结构存在可以使用
--启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

insert into serv_user_bak1
SELECT * FROM
OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:/test.xls;','select * from [Sheet1$]')

---使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure