1.先在查詢分析器里執(zhí)行

exec sp_configure "show advanced options",1
?reconfigure
?exec sp_configure "Ad Hoc Distributed Queries",1
?reconfigure

2.存儲(chǔ)過程

ALTER procedure [dbo].[sp_ImpCustomer]
?@ExcelPath varchar(1000),
?@SheetName varchar(100)='Sheet1',
?@ExcelField varchar(500)='帳號(hào),姓名,機(jī)構(gòu),經(jīng)紀(jì)人,區(qū)域經(jīng)理,銷售行政,電話',
?@TableName varchar(100)='t_busi_callingcust'
as
begin
??? set nocount on
??? declare @strSql varchar(4000)
??? -- 打開開關(guān)?
?--exec sp_configure "show advanced options",1
?--reconfigure
?--exec sp_configure "Ad Hoc Distributed Queries",1
?--reconfigure
?-- 重啟sql full-text filter daemon launcher,sql server倆個(gè)服務(wù)
?IF EXISTS? (SELECT * FROM dbo.SysObjects WHERE ID = object_id(@TableName)
??AND OBJECTPROPERTY(ID, 'IsTable') = 1)
??TRUNCATE TABLE t_busi_callingcust -- 刪除表中的所有數(shù)據(jù)
?else
???? -- 創(chuàng)建表
??create table t_busi_callingcust(
??Accounts varchar(20) null,
??CustName varchar(50) null,
??Orgazition varchar(50) null,
??[Broker] varchar(50) null,
??DistrictManager varchar(50) null,
??SalesAdmin varchar(50) null,
??Tel varchar(30) null
??)
?SET @ExcelPath = '''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @ExcelPath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'''
?SET @strSql = 'insert into ' +@TableName+' select *?from opendatasource('+@ExcelPath+')...'+@SheetName+'$'
?--print @strsql
?EXEC(@strSql)
?set nocount off
end

總結(jié)

以上是生活随笔為你收集整理的SQL SERVER 导入EXCEL的存储过程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。