指定文件夹下结构相同的EXCEL文件批量导入到SQL数据库中指定的表

本文链接：https://blog.youkuaiyun.com/g_little_boy/article/details/6492486

本文介绍了一种使用SQL Server存储过程批量将指定文件夹下结构相同的Excel文件导入到SQL数据库指定表的方法。通过创建存储过程Sp_InputExceltoSQL，可以实现Excel文件路径的灵活配置，并能够自动创建目标表及进行数据导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL code

 -------------------------------------------------------------------------- --  Author : htl258(Tony) --  Date   : 2010-04-07 21:09:45 --  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)  --          Jul  9 2008 14:43:34  --          Copyright (c) 1988-2008 Microsoft Corporation --          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) --  Subject:将指定文件夹下结构相同的EXCEL文件批量导入到SQL数据库中指定的表 -------------------------------------------------------------------------- IF OBJECT_ID('Sp_InputExceltoSQL') IS NOT NULL     DROP PROC Sp_InputExceltoSQL GO CREATE PROC Sp_InputExceltoSQL @dir NVARCHAR(100),--EXCEL文件存放路径：如D:/ExcelFiles @tabname NVARCHAR(50) --定义导入到数据库中的表名 AS SET NOCOUNT ON DECLARE @cmd NVARCHAR(1000) CREATE TABLE #t([filename] NVARCHAR(1000)) IF RIGHT(@dir,1)<>'/'      SET @dir=@dir+'/' SET @cmd = N'dir "' + @dir + '*.xls" /B' INSERT #t EXEC master..xp_cmdshell @cmd DELETE #t WHERE [filename] IS NULL --在SQL中创建表: DECLARE @S nvarchar(MAX) IF OBJECT_ID(@tabname) IS NULL BEGIN     SELECT TOP 1 @S='SELECT TOP 0 * INTO '+ @tabname+' FROM OPENROWSET(             ''MICROSOFT.JET.OLEDB.4.0'', ''EXCEL 5.0;HDR=YES;IMEX=2;DATABASE='+@DIR+[filename]+'''             ,''SELECT * FROM [Sheet1$]'')'     FROM #t       EXEC(@S) END --开始导入目录下的文件 SET @S = '' SELECT @S = @S + 'INSERT '+@tabname+' SELECT * FROM OPENROWSET(     ''MICROSOFT.JET.OLEDB.4.0'', ''EXCEL 5.0;HDR=YES;IMEX=2;DATABASE='+@DIR+FILENAME+'''     ,''SELECT * FROM [Sheet1$]'')'   FROM #t       EXEC(@S) SET NOCOUNT OFF GO EXEC Sp_InputExceltoSQL 'D:/ExcelFiles','Tony'  SELECT * FROM Tony /* a                      b                      c                      d ---------------------- ---------------------- ---------------------- ---------------------- 1                      2                      3                      4 5                      6                      7                      8  (2 行受影响) */