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

最新推荐文章于 2024-07-20 16:40:31 发布

Cana

最新推荐文章于 2024-07-20 16:40:31 发布

阅读量286

点赞数

文章标签： excel 数据库 sql microsoft database sql server

本文链接：https://blog.csdn.net/g_little_boy/article/details/6492486

版权

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 行受影响) 
   
   */