Excel文件导入SqlServer最简单方法！（超简单、超实用）

最新推荐文章于 2024-08-09 11:53:46 发布

敦厚的曹操

最新推荐文章于 2024-08-09 11:53:46 发布

阅读量1.4w

点赞数 1

分类专栏： 2.【Asp.Net】 1.【Sql Server】

本文链接：https://blog.csdn.net/dxnn520/article/details/8451820

版权

2.【Asp.Net】同时被 2 个专栏收录

130 篇文章 3 订阅

订阅专栏

1.【Sql Server】

77 篇文章 4 订阅

订阅专栏

-- 开启Ad Hoc Distributed Queries组件
--exec sp_configure 'show advanced options',1
--reconfigure
--exec sp_configure 'Ad Hoc Distributed Queries',1
--reconfigure

-- 关闭Ad Hoc Distributed Queries：
--exec sp_configure 'Ad Hoc Distributed Queries',0
--reconfigure
--exec sp_configure 'show advanced options',0
--reconfigure

--select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\dl.xls', 'select * from [Sheet1$] where f1 is not null')

--select * from OpenRowSet('Microsoft.ACE.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\dl.xls', 'select * from [Sheet1$]')

SELECT * INTO Temp_Upload1

-- 开启Ad Hoc Distributed Queries组件
--exec sp_configure 'show advanced options',1
--reconfigure
--exec sp_configure 'Ad Hoc Distributed Queries',1
--reconfigure




-- 关闭Ad Hoc Distributed Queries：
--exec sp_configure 'Ad Hoc Distributed Queries',0
--reconfigure
--exec sp_configure 'show advanced options',0
--reconfigure 


--select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\dl.xls', 'select * from [Sheet1$] where f1 is not null') 


--select * from OpenRowSet('Microsoft.ACE.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\dl.xls', 'select * from [Sheet1$]')   




SELECT * INTO Temp_Upload1


FROM   OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\dl.xls',sheet1$)






--SELECT * INTO Temp_Upload1


--FROM   OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\dl.xls',sheet1$)










--delete from [Card].[dbo].[Temp_Upload1] where f1 is null
--delete from [Card].[dbo].[Temp_Upload1] where f1=''
--delete from [Card].[dbo].[Temp_Upload1] where f11 is null
--SELECT *  FROM [Card].[dbo].[Temp_Upload1]

FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\dl.xls',sheet1$)

--SELECT * INTO Temp_Upload1

--FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\dl.xls',sheet1$)

--delete from [Card].[dbo].[Temp_Upload1] where f1 is null
--delete from [Card].[dbo].[Temp_Upload1] where f1=''
--delete from [Card].[dbo].[Temp_Upload1] where f11 is null
--SELECT * FROM [Card].[dbo].[Temp_Upload1]