SQL SERVER 导入EXCEL的存储过程

1.先在查询分析器里执行

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

2.存储过程

ALTER procedure [dbo].[sp_ImpCustomer]
@ExcelPath varchar(1000),
@SheetName varchar(100)='Sheet1',
@ExcelField varchar(500)='帐号,姓名,机构,经纪人,区域经理,销售行政,电话',
@TableName varchar(100)='t_busi_callingcust'
as
begin
    set nocount on
    declare @strSql varchar(4000)
    -- 打开开关
--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俩个服务
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(@TableName)
  AND OBJECTPROPERTY(ID, 'IsTable') = 1)
  TRUNCATE TABLE t_busi_callingcust -- 删除表中的所有数据
else
     -- 创建表
  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

巴特西

SQL SERVER 导入EXCEL的存储过程

最新文章

热门文章