MS-SQL通过存储过程获取Excel数据

  MS-SQL通过存储过程获取Excel数据

    公司最近业务，需要与其他ERP软件进行数据上的交接，对方不允许直接访问数据库的形式，因此考虑通过Excel的形式对数据进行读取，感觉使用MS-SQL直接读取比较简单，遂干之。

一、确认Excel表格式

   首先，需要对于Excel表的格式进行确认，包括表各列的数据类型，格式以及定义的格式表的名称。如下图：

二、MS-SQL设置

   因为获取的Excel版本是Excel2016，在MS-SQL中获取需要安装AccessDataBaseEngine，下载链接为：https://www.microsoft.com/en-us/download/details.aspx?id=13255

安装完成后，可以在MS-SQL——>服务器对象——>链接服务器——>访问接口中看到Microsoft.ACE.OLEDB.12.0，如下图：

二、编写存储过程

Create PROCEDURE [dbo].[includeExcel_Business](
@ExcelPath nvarchar(100),                              --Excel全路径名称，例如F:/Excel/businessExcel.xlsx
@SheetName nvarchar(100)                                --表空间的名称
)
    
AS
BEGIN
    declare @sql nvarchar(1000)
    declare @sertalCode int
    declare @node_name nvarchar(100)
    declare @supplier_name nvarchar(100)
    declare @supplier_code nvarchar(13)
    declare @wholesaler_name nvarchar(100)
    declare @wholesaler_code nvarchar(100)
    declare @business_type nvarchar(10)
    declare @business_property nvarchar(10)
    declare @reg_node nvarchar(10)
    declare @tel nvarchar(20)
    declare @legal_respresent nvarchar(100)
    declare @tran_Code nvarchar(100) 
    begin try
            create table #tmp(                               --创建临时表用于获取Excel中的数据
                    node_name nvarchar(50) NULL,
                    wholesaler_name nvarchar(50) NULL,
                    supplier_name nvarchar(50) NOT NULL,
                    business_type nvarchar(1) NULL,
                    business_property nvarchar(2) NULL,
                    reg_node nvarchar(50) NULL,
                    tel nvarchar(20) NULL,
                    legal_respresent nvarchar(20) NULL
            )
     exec sp_configure "show advanced options",1
     reconfigure
     exec sp_configure "Ad Hoc Distributed Queries",1
     reconfigure;
            set @sql='insert into #tmp SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 5.0;HDR=YES;DATABASE='+@ExcelPath+''','+
@sheetName+'$)'; --动态根据Excel名与表空间名读取Excel中的数据
            exec (@sql);
    set @sertalCode=1;
    declare businessCursor Cursor for select node_name,supplier_name,wholesaler_name,business_type,business_property,reg_node,tel,legal_respresent 
from #tmp;  --利用游标对临时表的数据进行处理放到真实的表中
    open businessCursor;
    fetch next from businessCursor into @node_name,@supplier_name,@wholesaler_name,@business_type,@business_property,@reg_node,@tel,@legal_respresent;
    while(@@FETCH_STATUS=0)
        begin
            exec getSertalCode @sertalCode,@wholesaler_code output;
            set @sql='insert into dbo.SERTAL_CODE(name,alias,gentype,nolength,initValues,curvalue,step) values(''追溯码:'+@supplier_name+''','
+@supplier_code+',''1'',''7'',''1'',''1'',''1'')';
            exec (@sql);
            if(@supplier_name is not null)
                begin
                    if  exists(select * from dbo.base_node_info where base_name=@supplier_name)
                        begin
                            select @supplier_code=base_code from dbo.base_node_info where base_name=@supplier_name
                        end 
                    
                end
            else
                begin
                    set @supplier_code='999999999';        
                end
            insert into dbo.super_mar_supplier_base_info(id,node_id,node_name,wholesaler_id,wholesaler_name,supplier_id,supplier_name,reg_id,
property,type,record_date,legal_pepresent,tel,update_time,business_type)
            values(newID(),'370303902',@node_name,@supplier_code,@supplier_name,@wholesaler_code,@wholesaler_name,@reg_node,@business_property,
@business_type,GETDATE(),@legal_respresent,@tel,GETDATE(),'1');
            set @tran_Code='追溯码:'+@wholesaler_name;
            insert into dbo.SERTAL_CODE(name,alias,gentype,nolength,initValues,curvalue,step)values(@tran_Code,@wholesaler_code,'1','7','1','1',
'1');
            fetch next from businessCursor into @node_name,@supplier_name,@wholesaler_name,@business_type,@business_property,@reg_node,@tel,
@legal_respresent;
        end
    close businessCursor  
            --撤销游标  
    DEALLOCATE businessCursor

    Drop table #tmp;
    end try
    begin catch
            SELECT   
                 ERROR_NUMBER() AS ErrorNumber,  
                 ERROR_SEVERITY() AS ErrorSeverity,  
                 ERROR_STATE() as ErrorState,  
                 ERROR_PROCEDURE() as ErrorProcedure,  
                 ERROR_LINE() as ErrorLine,  
                 ERROR_MESSAGE() as ErrorMessage;  
            IF @@TRANCOUNT > 0  
                 ROLLBACK TRANSACTION;  
    end catch
END