利用SQL脚本向数据库导入EXCEL中的数据

-- =============================================

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