关于大数据量的导出
前些天做项目需要大数据量的导出,于是考虑应该用什么方法来实现比较好。经过分析,由于只需把文件导出,上传给海关就可以了,而且这个数据海关会有专门的程序来读取,只要符合他们要求的格式就可以了,于是决定使用bcp命令进行导出,这样效率应该是最好的吧。最后就写成了存储过程,这样方便进行调用。
存储过程如下:
CREATE PROCEDURE lihin_SpExportXLS
@SqlStr nvarchar(1000),
--@Server nvarchar(16),
--@UserName nvarchar(16),
--@PWD nvarchar(16),
@XLSPath nvarchar(1000),
@PageCount int
--WITH ENCRYPTION
AS
declare @cmd nvarchar(1000),
@TempTabName nvarchar(30),
@RstSqlStr nvarchar(1000),
@nFile int,
@nRow int,
@Server nvarchar(16),
@UserName nvarchar(16),
@PWD nvarchar(16)
set @Server='localhost'
set @UserName='sa'
set @PWD='lihinsoft'
set @TempTabName='##TempTab'+DATENAME(yyyy, getdate())+DATENAME(MM, getdate())+
DATENAME(dd, getdate())+DATENAME(hh, getdate())+DATENAME(mi, getdate())+
DATENAME(ss, getdate())+DATENAME(ms, getdate())
--print @TempTabName
set @nFile=1
if(@PageCount>65535)
set @PageCount=65535
set @cmd = 'SELECT * INTO '+@TempTabName+' from ('+@SqlStr+') TabSource'
--print @cmd
EXEC (@cmd)
export:
print '********** ********** ********** xls file'+ Convert(nvarchar(8),@nFile) +' ********** ********** **********'
set @RstSqlStr='select top '+Convert(nvarchar(8),@PageCount)+' * from '+@TempTabName
set @cmd='bcp "'+ @RstSqlStr +'" queryout "'+ @XLSPath+Convert(nvarchar(8),@nFile)+'.xls' +'" -c -S"'+
@Server +'" -U"'+ @UserName +'" -P"'+ @PWD +'"'
EXEC master..xp_cmdshell @cmd
SET ROWCOUNT @PageCount
set @cmd = 'delete from '+@TempTabName
EXEC (@cmd)
SET ROWCOUNT 0
set @cmd='select * from '+@TempTabName
EXECUTE getnRow @TempTabName, @@nRow=@nRow OUTPUT
--print Convert(nvarchar(8), @nRow)
IF(@nRow > 0)
begin
set @nFile=@nFile+1
goto export
end
set @cmd = 'drop table [dbo].['+@TempTabName+']'
--print @cmd
EXEC (@cmd)
GO
这是导出到excel的,下面的可以导出到access
/*--调用示例
lihin_SpExportAccs @sqlstr='select * from sysobjects'
,@path='c:\',@fname='test.mdb',@tbname='sysobjects'
--*/
CREATE PROCEDURE lihin_SpExportAccs
@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250), --文件名
@tbname varchar(250)='', --要创建的表名,默认为文件名
@password varchar(20)='', --ACCESS数据库的密码
@s_user sysname='sa', --如果提示连接错误,则需要提供登陆sql服务器的用户名及密码
@s_password varchar(50)='lihinsoft'
as
set nocount on
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname='temp.mdb'
if isnull(@tbname,'')='' set @tbname=replace(@fname,'.','#')
--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--数据库创建
select @sql=@path+@fname
,@constr='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@sql
+case isnull(@password,'') when '' then ''
else ';Jet OLEDB:Database Password='+@password end
if not exists(select 1 from #tb where a=1)
begin
exec @err=sp_oacreate 'ADOX.Catalog',@obj out
--if @err<>0 goto err
exec @err=sp_oamethod @obj,'Create',null,@constr
--if @err<>0 goto err
exec @err=sp_oadestroy @obj
--if @err<>0 goto err
end
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto err
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto err
--删除现有的表
set @sql='drop table ['+@tbname+']'
exec @err=sp_oamethod @obj,'execute',@out out,@sql
--if @err<>0 goto err
--导入表
declare @t_tbname sysname
set @t_tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@t_tbname+'] from('+@sqlstr+') a'
exec(@sql)
if isnull(@s_user,'')=''
set @sql='SELECT * into ['+@tbname
+'] FROM [ODBC;Driver=SQL Server;Server='+ @@servername
+';database=tempdb].['+@t_tbname+']'
else
set @sql='SELECT * into ['+@tbname
+'] FROM [ODBC;Driver=SQL Server;Server='+ @@servername
+';Uid='+@s_user+';Pwd='+isnull(@s_password,'')
+';database=tempdb].['+@t_tbname+']'
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto err
exec @err=sp_oadestroy @obj
set @sql='drop table ['+@t_tbname+']'
exec(@sql)
return
err:
exec sp_oageterrorinfo 0,@src out,@desc out
if @obj<>0 exec @err=sp_oadestroy @obj
_exit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr
GO
posted on 2005-06-21 15:24 一切为了DotNet 阅读(677) 评论(0) 编辑 收藏 举报