批量将Access 2000 的mdb文件导入到SqlServer 2005中-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介:

       场景:需要将一批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:


1
2
3
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 INNER JOIN  sysindexes AS 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);

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

1
select from importdb

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

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

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

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

1
select count(*) from main

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


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


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

范例3:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--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,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章