求一个批量导入导出sql server 的方案(100分),有经验者,高人进!

liyong1983 2008-01-14 01:17:36
本人在做一个系统时遇到一个批量导入导出数据功能。请大家看完下面的内容,再做评论。如果问题,未描述清楚,请指出。
问题:用户在一个数据库中存了数据,各个用户存在数据库中的数据没有任何关系,用户数可能有几百,现要把单个用户的数据导出来,存储起来,用户可以把以前导出的数据重新导入到数据库。导入时,现有市场上使用的机器(最低档配置:cpu:c700 内存:128MB 硬盘:5G空间),需要在1分钟内完成。最低要求不能超过2分钟。
前题条件:
1、要导入导出的数据库里面有200-500个数据表。第个表里面可能有大容量的text(有可能存储超过8000个汉字的数据)和image字段(存图像)。第个数据表都有相同的字段“用户名”,供导入导出时,区分需提取的数据。也就是各个用户都在数据库中存有一份数据,导出时把该用户的数据提取出来。导入时,把导出的用户数据替换要导入用户的数据。
2、导入时可能发生在一个sql server的数据库或局域网内的两台机器的相同数据库之间。
3、要导入数据的规模:所导出来的记录数在1W行-3W行。各个表的记录分布没有规律,存在大容量字段,字段可能是图像或文本(超过8000个字符的文本)。
4、数据库必需是sql server 2000。

要实现的功能(数据导出,数据导入):
1、数据导出:从一个数据库的所有表中,抽取一“用户”的数据导出存储起来,供导入时使用。存储的方式,最好存到sql server中。这样便于客户机从服务器中取数据。也可以采取其它方式。
2、数据导入:把导出时一个用户的数据,替换掉数据库中另一个用户的数据。

3、数据导入的时间须在1分钟之内,机器硬件最低要求要是(cpu:c700 内存:128MB 硬盘:5G空间),操作系统要在win 2000以上的版本,加了这一条,问题就比较麻烦了。
4、不能使用数据库的备份和恢复功能。也就是不能改变生成新的数据库中的数据结构。
5、必须能在开发工具里面调用这两个方功能,最好这两个功能做成存储过程调用。如:.net,delphi中调用。


各位高手,看到这里,有没有好的解决方案,哪位朋友做过这方面的内容,请多指导。谢谢!

本人现在已实现导入导出功能,在配置256内存的机器上面,需要2-3分钟。实在没有办法把速度提高到1分钟。

小弟不才,试过的方法如下:
1、先把一个数据库的数据用存储过程把里面的数据查出来,生成sql 语句,存储到image字段中,导入时先把导入的用户数据删除,再调用字段中的sql 代码,此方法记录总数在2000条时,速度还可以,超过时,不管机器性能如何,速度成正比增加,在有15000行记录时,(cpu:c700 内存:128MB 硬盘:5G空间)配置的机器,导入时需要8-10分钟。双核+1G内存需要2分钟。
2、改进第一种方法,因为第一种方法,实际,sql server 所用时间,全花在编绎1W多行代码上,现把导入的存储过程行数,要执行的sql 语句减少,方案:另建一个相同结
构的数据库(简称备份库,供后面引用),导出时,把数据存到备份库中,导入时,再从备份库取数据用 insert into *** select ***把数据插入进去。
此方法明显比第一种速度快,在双核的机器,1G内存下,只需要几秒钟(除第一次,因第一次如果没设sql的内存以及数据库文件的大小时,需要将近1分钟左右)。但在(cpu:c700 内存:128MB 硬盘:5G空间)配置的机器上,仍需要2-3分钟。

各位高手,看了小弟的方案后,有什么更好的改进意见或方案。如果实在实现困难,局域网内的两台机器的相同数据库之间进行数据导入导出暂不考虑。本人还未用dts,bcp工具进行测试,因为这两种工具都需要生成文本文件,如果数据库中有300多个表,中间就需要生成300多个文本文件,这样,系统处理起来,本人推测,速度应该跟不上。注:机器配置在256内存的机器。而且在导入数据时,本人估计删除以前的数据需要10-15秒。
本人一直用sql server 2000,并未用过2005,不知2005里面有没有提供新的导入功能。





...全文
1013 点赞 收藏 27
写回复
27 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
刘兄弟 2011-11-16
楼主能不能提示下你怎么做的哦
回复
liyong1983 2008-03-05
本人到现在还没找到真正速度快的方案,不过找到了替换方案,谢谢大家的回复。
回复
ybhzf 2008-02-26
用bcp或者dts都可以速度挺快的。dts不需要中间文件,bcp需要。
或者使用ado.net 2.0的datareader模式集合net 2.0中大容量拷贝类(类似bcp的一个类)来实现记录复制。
回复
liyong1983 2008-01-28
JiangHongTao
蒋洪涛
等 级:
发表于:2008-01-18 22:35:1411楼 得分:0
既能你可以将数据导出到同一个数据库服务器的不同数据库,为什么不可以在每个表中增加状态字段,导出时置为0,导入时置为1。
你没有说明你导出、导入的折腾是为了什么?


此种方案不行。
回复
liyong1983 2008-01-28
引用:邹建
发表于:2008-01-20 02:54:0816楼 得分:0
1. 内存太小了点, 所以这个无法保证

2. 一般来说, 数据导入/导出有这样两种设计上的区别
a) 如果直接用 sql 语句(存储过程也算 sql 语句), 那就要求导入/导出的文件必须在 sql 服务器, 或者在 sql 服务器能访问的共享目录上(要求解决共享权限的问题)
b) 如果写程序, 则要求程序去实现导入/导出, 则文件位置只需要在程序所在的电脑上即可(当然, 也可以是程序能够访问的共享目录中,这也要求共享目录的写权限)


所以, 楼主首先要根据能够提供的环境来选择一种方法, 不能随便的优先选择方法 a

本人是做这个时,已经考虑到这两种情况,由于以前是用sql语言查询出sql语句导进去,不存在服务器本地的区别,后来改进时,本想采用access加程序把数据放到access中,由于改的程序很大,所以不采用,我们公司有一个软件就是采用access加程序(delphi)的方案,速度还可以,但数据量很大时还是有点慢,我采用方案a,如果是网络上两台机器,用得的链接服务器,程序实现是没有问题,不过,本人最近使用时,发现有一些问题,ODBC链接字串时有问题,有一部分机器无法使用,暂时还未查出原因。
综合各种方案,本人觉得采用数据库恢复是最快的选择,但改程序需要时间。

如果采用dts本人觉得程序是可以实现,不过还未测试,要生成200-300个文件,256内存的机器,我想应该不快,如果把所有数据导出到一个文件,不知速度怎么样。而且这种方案有一个问题,就是当字段是image时,就行不通了。
回复
fcuandy 2008-01-28
jf
回复
liangCK 2008-01-20
很好,很强大.
回复
zjcxc 2008-01-20
要折腾数据的话, 用DTS/SSIS, 或者程序容易, 其他比较不好折腾
回复
zjcxc 2008-01-20
再来讨论一下方法的问题
openrowset/opendatasource/bulk insert/xp_cmdshell + bcp
这几种方法都属于使用 sql 的方法, 除了最后的那个 xp_cmdshell + bcp 容易实现导出外, 其他的基本上只能做导入
所以一般来说用 sql 语句做导入还比较现实, 做导出就不行了


其实无论 2000 还是 2005, sql 自己都提供了数据导入/导出的方法, 2000 是DTS, 2005 是 SSIS, 只要在数据文件所在的电脑上部署DTS或者SSIS包, 就可以实现导入/导出, 楼主提到的转换也容易实现, 但 DTS 包实现多个文件的处理不太容易, 可以考虑用程序实现多个文件的读取, 把文件名以变量的方法传给DTS包, SSIS可以直接实现指定目录下文件的导入(用循环控件), 当然要求文件格式和接收导入的表结构相同

如果写程序, 建议使用 sqlbulkcopy 对象(要求ado.net 2.0)

如果手工, 建议 bcp , 它的速度算是最快的
回复
zjcxc 2008-01-20
再来讨论一下方法的问题
openrowset/opendatasource/bulk insert/xp_cmdshell + bcp
这几种方法都属于使用 sql 的方法, 除了最后的那个 xp_cmdshell + bcp 容易实现导出外, 其他的基本上只能做导入
所以一般来说用 sql 语句做导入还比较现实, 做导出就不行了


其实无论 2000 还是 2005, sql 自己都提供了数据导入/导出的方法, 2000 是DTS, 2005 是 SSIS, 只要在数据文件所在的电脑上部署DTS或者SSIS包, 就可以实现导入/导出, 楼主提到的转换也容易实现, 但 DTS 包实现多个文件的处理不太容易, 可以考虑用程序实现多个文件的读取, 把文件名以变量的方法传给DTS包, SSIS可以直接实现指定目录下文件的导入(用循环控件), 当然要求文件格式和接收导入的表结构相同

如果写程序, 建议使用 sqlbulkcopy 对象(要求ado.net 2.0)

如果手工, 建议 bcp , 它的速度算是最快的
回复
zjcxc 2008-01-20
再来讨论一下方法的问题
openrowset/opendatasource/bulk insert/xp_cmdshell + bcp
这几种方法都属于使用 sql 的方法, 除了最后的那个 xp_cmdshell + bcp 容易实现导出外, 其他的基本上只能做导入
所以一般来说用 sql 语句做导入还比较现实, 做导出就不行了


其实无论 2000 还是 2005, sql 自己都提供了数据导入/导出的方法, 2000 是DTS, 2005 是 SSIS, 只要在数据文件所在的电脑上部署DTS或者SSIS包, 就可以实现导入/导出, 楼主提到的转换也容易实现, 但 DTS 包实现多个文件的处理不太容易, 可以考虑用程序实现多个文件的读取, 把文件名以变量的方法传给DTS包, SSIS可以直接实现指定目录下文件的导入(用循环控件), 当然要求文件格式和接收导入的表结构相同

如果写程序, 建议使用 sqlbulkcopy 对象(要求ado.net 2.0)

如果手工, 建议 bcp , 它的速度算是最快的
回复
zjcxc 2008-01-20
1. 内存太小了点, 所以这个无法保证

2. 一般来说, 数据导入/导出有这样两种设计上的区别
a) 如果直接用 sql 语句(存储过程也算 sql 语句), 那就要求导入/导出的文件必须在 sql 服务器, 或者在 sql 服务器能访问的共享目录上(要求解决共享权限的问题)
b) 如果写程序, 则要求程序去实现导入/导出, 则文件位置只需要在程序所在的电脑上即可(当然, 也可以是程序能够访问的共享目录中,这也要求共享目录的写权限)


所以, 楼主首先要根据能够提供的环境来选择一种方法, 不能随便的优先选择方法 a
回复
dawugui 2008-01-19
/*===================  导入/导出 Excel 的基本方法 ===================*/

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)


/*===================================================================*/
--从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from 表


--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"'

--导出查询的情况
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"'


/*--说明:
c:\test.xls 为导入/导出的Excel文件名.要求文件在SQL服务器上,或者SQL服务器可以访问的共享目录上
sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.
--*/



/*=================== 导入/导出 dBase 的基本方法 ===================*/

从dBase文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')

--如果导入数据并生成表
select * into 表 from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')


/*===================================================================*/
--如果从SQL数据库中,导出数据到dBase,如果dBase文件已经存在,就可以简单的用:
insert into
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
select * from 表


/*--说明:
DATABASE=c:\ c:\是dbf文件的存放目录
'select * from [test.dbf] test.dbf是指dbf文件名
--*/
回复
dawugui 2008-01-19

--数据导入导出(全).sql
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go

/*--实现数据导入/导出的存储过程

可以实现导入/导出 整个数据库/指定表 到文本文件

--邹建 2003.07(引用请保留此信息)--*/

/*--调用示例

--导出指定表
exec file2table @tbname=N'jobs,pub_info',@filename='c:\[@tbname].txt'

--导出所有表
exec file2table @filename='c:\[@dbname]_[@tbname].txt'

--导入所有表
exec file2table @filename='c:\[@dbname]_[@tbname].txt',@isout=0
--*/
create procedure File2Table
@tbname nvarchar(4000)='', --表名列表,如果不指定,则表示所有用户表
@filename nvarchar(1000)='', --导出的文件名,如果不指定,导出到SQL Server的默认备份目录\[@dbname]_[@tbname].txt,其中[@dbname]在处理中会替换为数据库名,[@dbname]会用表名代替
@isout bit=1, --1为导出(默认),0为导入
@username sysname='', --用户名,如果sql不允许使用NT验证方式登录,则必须指定
@password sysname='', --密码
@code char(1)=N'N' --文件编码,可以是n,c,N,W,作用参考bcp语法(如果是数据传输需要,建议用N)
as
declare @s nvarchar(4000)

if isnull(@code,N'') not in(N'n',N'c',N'N',N'W')
set @code='N'

--备份文件名
if isnull(@filename,N'')=N''
begin
select top 1 @filename=rtrim(reverse(filename))
from master.dbo.sysfiles
where name=N'master'
select @filename=stuff(@filename,1,charindex('\',@filename),N'')
,@filename=reverse(stuff(@filename,1,charindex('\',@filename),N''))
+N'\BACKUP\'+db_name()+N'_[@tbname].txt'
end
else
set @filename=replace(@filename,N'[@dbname]',db_name())

declare tb cursor local
for
select N'bcp "'+db_name()
+N'.'+quotename(user_name(uid))
+N'.'+quotename(name)
+N'"'
+case when @isout=1 then N' out' else N' in' end
+N' "'
+replace(@filename,N'[@tbname]',name)
+N'" /'+@code
+case when isnull(@username,N'')=N'' then N' /T'
else N' /U"'+@username
+N'" /P"'+isnull(@password,N'')+N'"'
end
from sysobjects
where xtype=N'U' and status>=0
and(isnull(@tbname,N'')=''
or charindex(','+name+',',','+@tbname+',')>0)
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec master..xp_cmdshell @s,no_output
fetch tb into @s
end
close tb
deallocate tb
go
回复
-狙击手- 2008-01-19
似乎BCP应该更快呀
回复
chengqscjh 2008-01-19
2楼的可以啊
回复
JiangHongTao 2008-01-18
既能你可以将数据导出到同一个数据库服务器的不同数据库,为什么不可以在每个表中增加状态字段,导出时置为0,导入时置为1。
你没有说明你导出、导入的折腾是为了什么?
回复
rouqu 2008-01-18
可否问一下 这样做的意义/目的
回复
rouqu 2008-01-18
怎么感觉要把当前数据库分布到所有客户端上?为什么每台客户端上都要安装SQL服务器呢?
回复
rouqu 2008-01-18
LZ 你作文啊???
回复
加载更多回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2008-01-14 01:17
社区公告
暂无公告