批量将Access 2000 的mdb文件导入到SqlServer 2005中

最新推荐文章于 2024-04-29 23:47:34 发布

weixin_34239169

最新推荐文章于 2024-04-29 23:47:34 发布

阅读量280

点赞数 1

文章标签：数据库 shell

原文链接：https://yq.aliyun.com/articles/521935

版权

场景：需要将一批Access 2000的mdb文件导入到Sql server 2005，Access数据库文件大概有几百个，全部结构相同，有同样的表名main0;总数据大概在5000w

显然用DTS数据导入导出是不太可能的事情，每个ACCESS表导入的时间都在10分钟到30分钟不等，人不可能守着点鼠标赛。

那么只有用sql语句导入了。

本打算采用bcp来操作，bcp导一个1000万行的txt文本也用不了多久，但是bcp怎么导入mdb文件还真没试过。（有操作过的请指点下）

假设SQL中创建了与mdb的main0表同结构的表，表名也为main0;导入access数据库到SQL的语句为

范例1：

 
         Insert  
         into   
         main0   
         select  
         * 
        
         FROM  
         OPENDATASOURCE ( 
         'Microsoft.Jet.OLEDB.4.0' 
         , 
        
         'Data Source="d:\mdb\20131222\xxxxxxxx.mdb";User ID=Admin;Password='  
         )...main0 ;

为了查看已经导入了多少mdb，可以先建立一张表。

范例2：

 
    
         create  
         table  
         importdb( 
        
 
         dbname nvarchar( 
         max 
         ), 
        
 
         dbcount  
         int 
        
 
         ) 
        
 
         DECLARE  
         @countall  
         int 
        
 
         DECLARE  
         @countnow  
         int 
        
 
         set  
         @countnow=0 
        
 
         --1989 before 
        
 
         Insert  
         into   
         main0   
         select  
         * 
        
 
         FROM  
         OPENDATASOURCE ( 
         'Microsoft.Jet.OLEDB.4.0' 
         , 
        
 
                
         'Data Source="d:\zk\20131222\xxxxx.mdb";User ID=Admin;Password='  
         )...main0 ; 
        
 
         set  
         @countall=( 
         SELECT   
         b. 
         rows    
         FROM       
         sysobjects  
         AS  
         a  
         INNER  
         JOIN   
         sysindexes  
         AS  
         b  
         ON  
         a.id = b.id   
         WHERE    
         (a.type =  
         'u' 
         )  
         AND  
         (b.indid  
         IN  
         (0, 1)  
         and  
         (a. 
         name 
         = 
         'main0' 
         ) ) ) 
        
 
         set  
         @countnow=@countall-@countnow 
        
 
         Insert  
         into  
         importdb(dbname,dbcount)  
         values 
         ( 
         'xxxxx.mdb' 
         ,@countnow); 
        
 
  

这样可以在导入的过程中随时

 
         select  
         *  
         from  
         importdb

来查看已经导入了多少mdb文件，并且每个mdb文件导入了多少条数据。

这里没有使用 select count(*) from main0 而是使用

 
    
         SELECT   
         b. 
         rows    
         FROM       
         sysobjects  
         AS  
         a  
         INNER  
         JOIN   
         sysindexes  
         AS  
         b  
         ON  
         a.id = b.id   
         WHERE    
         (a.type =  
         'u' 
         )  
         AND  
         (b.indid  
         IN  
         (0, 1)  
         and  
         (a. 
         name 
         = 
         'main0' 
         ) ) 
        
 
  

来统计表里的行数，原因是这样速度快的多，恩，不能说是快的多，因为这个是秒出结果，而

 
         select  
         count 
         (*)  
         from  
         main

慢的不能忍受，尤其是正在导入数据的时候做这个查询。

知道一个mdb怎么导入了，那么如何批量导入呢？

先遍历目录下的所有mdb文件，把遍历结果写入到一个临时表中；然后做个游标，查询这个临时表，每查到一行，数据表里的内容（mdb文件名）到变量中，进行组装sql语句；然后exec这个sql语句进行导入操作。

范例3：

 
         --Write By Skybug 
        
         -- 2013-12-22 
        
         --导入指定目录下的所有mdb到SQL server中 
        
         CREATE  
         TABLE  
         #( 
        
         files nvarchar( 
         max 
         ))  
         --创建临时表# （这个临时表的表名是#不是shell的注释符哈） 
        
         insert  
         #  
         exec  
         xp_cmdshell  
         'dir /B d:\mdb\20131222\*.mdb'   
         --遍历所有的mdb插入另时临时表 
        
         declare  
         @filesname  
         varchar 
         (200)  
         --定义变量mdb文件名 
        
         declare  
         @cmd1  
         varchar 
         (2000)   
         --定义变量 需要执行的sql串 
        
         DECLARE  
         filesname  
         CURSOR  
         FOR 
         --定义游标 
        
         select  
         [files]  
         from  
         # 
        
         Open  
         filesname 
         --打开游标 
        
         FETCH  
         NEXT 
        
         FROM  
         filesname 
        
         into  
         @filesname 
        
         WHILE @@FETCH_STATUS = 0 
        
         BEGIN 
        
         set  
         @cmd1= 
         'Insert into  main0  select * FROM OPENDATASOURCE (' 
         + 
         '' 
         '' 
         + 
         'Microsoft.Jet.OLEDB.4.0' 
         + 
         '' 
         '' 
         + 
         ',' 
         + 
         'Data Source="d:\mdb\20131222\'+@filesname+' 
         "; 
         User  
         ID=Admin; 
         Password 
         = 
         '+' 
         '' 
         '+'  
         )...main0 ; 
         ' 
        
         exec (' 
         '+@cmd1+' 
         '); 
        
         --print @cmd1 
        
         FETCH  
         NEXT 
        
         FROM  
         filesname 
        
         into  
         @filesname 
        
         end 
        
         CLOSE  
         filesName 
         --关闭游标 
        
         DEALLOCATE  
         filesName 
        
         drop  
         table  
         #  
         --删除临时表

这样就能遍历目录下的所有mdb文件并执行导入了。

如果需要随时查看导入进度，可以参照范例2，在while循环中每次游标移动前，把@filesname 和@countnow 写到表中用来查看进度。

————————————————————————————————————————————

需要注意的：在写范例三中的@cmd1的时候，把自己给搞晕了；将范例1中的sql语句转成@cmd1字符串的时候，需要注意，原SQL语句里就有'单引号；在组装@cmd1SQL语句字符串的时候，用单引号分成几段加起来，语句中的单引号用''''4个单引号来代替。

既：原语句如果是

aaa bb ccc 'ddddd' eeee

组装字符串的时候就是

'aaa bbb ccc '+''''+'dddd'+''''+'eeee'

本文转自天山三害 51CTO博客，原文链接：http://blog.51cto.com/skybug/1343819，如需转载请自行联系原作者

weixin_34239169

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
批量将Access 2000 的mdb文件导入到SqlServer 2005中

场景：需要将一批Access 2000的mdb文件导入到Sql server 2005，Access数据库文件大概有几百个，全部结构相同，有同样的表名main0;总数据大概在5000w显然用DTS数据导入导出是不太可能的事情，每个ACCESS表导入的时间都在10分钟到30分钟不等，人不可能守着点鼠标赛。那么只有用sql语句...
复制链接

扫一扫