场景：需要将一批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
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);
|
这样 可以在导入的过程中随时
|
1
|
select
*
from
importdb
|
来查看已经导入了多少mdb文件，并且每个mdb文件导入了多少条数据。
这里没有使用 select count(*) from main0 而是使用
|
1
|
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'
) )
|
来统计表里的行数，原因是这样速度快的多，恩，不能说是快的多，因为这个是秒出结果，而
|
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'
