-- =============================================
if object_id('sp_procExcelImport','p') is not null
drop procsp_procExcelImport
go
create PROCEDURE[dbo].[sp_procExcelImport]
@strExcelPathnvarchar(1000) = NULL, --Excel的绝对路径
@strExcelSheetNamenvarchar(50) = 'Sheet1', --Excel的工作表名,不用加$ 符号
@strSqlTableNamenvarchar(100) = NULL, --导入表名
@table_isdelbit = 1, --是否删除表
@is_pass varchar(20) output --是否成功
AS
set nocount on
--开启导入功能
exec sp_configure'show advanced options',1
reconfigure
exec sp_configure'Ad Hoc Distributed Queries',1
reconfigure
--允许在进程中使用ACE.OLEDB.12
exec master.dbo.sp_MSset_oledb_propN'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--允许动态参数
exec master.dbo.sp_MSset_oledb_propN'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
begin try
if (not object_id(@strSqlTableName) is null) and @table_isdel=1
exec('drop table '+@strSqlTableName)
DECLARE @strSql nvarchar(4000)
/* 导入*/
if charindex('.xlsx',@strExcelPath,0)>0
SET @strExcelPath = '''Microsoft.Ace.OLEDB.12.0'',''Data Source="' + @strExcelPath + '";User ID=Admin;Password=;Extendedproperties=Excel 8.0'''
else
SET @strExcelPath = '''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @strExcelPath + '";UserID=Admin;Password=;Extended properties=Excel 8.0'''
--将数据存放到临时表(一)
if (object_id(@strSqlTableName) is null)
begin
SET @strSql =
'SELECT *INTO '+@strSqlTableName+' FROM '+
'OpenDataSource('+ @strExcelPath +')...'+ @strExcelSheetName +'$ '
end
else
begin
SET @strSql =
'insert INTO'+@strSqlTableName+' SELECT * FROM '+
'OpenDataSource('+ @strExcelPath +')...'+ @strExcelSheetName +'$ '
end
set @is_pass='导入成功!'
EXEC(@strSql)
end try
begin catch
set @is_pass='导入失败!'
end catch
go
/*
调用存储过程的方法,按顺序的参数依次是:
1.EXCEL的路径,
2.sheet,
3.导进去的表名,
4.是否删除表,
5.输出参数,是否成功
*/
declare @avarchar(20)
exec sp_procExcelImport'd:\job.xlsx','sheet3','job','1',@a output
select @a