|
前几天,公司ERP要导出数据,由于数据量比较大,利用jxl或者poi导出根本导不出来,tomcat也设置了虚拟内存之类的东西,一导出excel数据服务器的cpu利用率绝对是百分百啊,要知道这是双核1.8G的CPU,1T的硬盘等,也是比较有名的服务器啊!公司的业务比较复杂,sql语句还不敢乱改,实在没办法了,眼泪哗哗的!但是要知道压力越大,潜力越大,压力是他娘,不对,压力像弹簧,你弱他就强!^_^,找度娘去,看到使用bcp导出数据,好东西试试吧!
先看一下,bcp的用法:?bcp?{dbtable?|?query}?{in??out??queryout??format}?数据文件
??[-m?最大错误数]?????????????-f?格式化文件?????????-e?错误文件
??-F?首行???????????????????-L?末行-b?批大小-n?本机类型???????????????-c?字符类型-w?宽字符类型-N?将非文本保持为本机类型?-V?文件格式版本?????-q?带引号的标识符-C?代码页说明符???????????-t?字段终止符???????-r?行终止符-i?输入文件-o?输出文件-a?数据包大小-S?服务器名称-U?用户名-P?密码-T?可信连接-v?版本-R?允许使用区域设置-k?保留空值-E?保留标识值-h"加载提示"??????????????-x?生成xml?格式化文件]
首先在服务器上要安装sql2005、sql2008等其他版本的sqlserver数据库
安装sql2005、sql2008等其他版本的sqlserver数据库
安装sql2005、sql2008等其他版本的sqlserver数据库
重要事情说3遍^_^
其次要开启组件 'xp_cmdshell',要不就报错,因为为了安全,sql默认是关闭的.
错误信息:在执行上述命令的时候可能会报错 ： 错误提示：消息 15281，级别 16，状态 1，过程 xp_cmdshell，第 1 行
在sql2005和sql2008的图形界面开启就不写了,我们用语句格式开启
-- 允许配置高级选项 ?
EXEC master.sys.sp_configure 'show advanced options',1 ?
-- 重新配置 ?
RECONFIGURE ?
-- 启用xp_cmdshell ?
EXEC master.sys.sp_configure 'xp_cmdshell',1 ?
--重新配置 ?
RECONFIGURE
我们只讲一种查询导出csv格式的文件的情况，其他的自己百度,(-t,)表示字段之间用逗号连接，-T表示安全连接
EXEC master..xp_cmdshell 'bcp "select username(列名1),password(列名2) from 数据库名.dbo.表名" queryout c:test.csv -c -t,-T'
大体解释一下，为什么建议导出csv格式的文件呢，因为为了计算分析方便，大部分人喜欢用excel进行统计汇总分析，而excel又有excel2003、excel2007等，格式不一样而且excel2003只能显示65535行数据，而csv文件可以用excel打开方便操作。
有人说了，能不能加上汉字列名呢，绝对可以，如下写法
bcp " select ''用户名''，''密码'' union all select username,password from 数据库名.dbo.表名" queryout c:test.csv -c -t,0); line-height:1.5!important">'
又有人说了，如果导出的数据中有为空的话，csv文件会被截断，显示不全数据啊，没关系，假设密码有为空的用户，我们写成char(32)，这个表示一个空格，再用excel打开csv文件时就不会显示不全数据，这是一个没有办法的办法，如果大家有好的方法，请贴出来告诉我一声，谢谢了！
如下写法
''密码'' union all password)=0 then ''=char(32)'' else password end as from 数据库名.dbo.表名" queryout c:test.csv -c -t,0); line-height:1.5!important">'
还有人说了，我能不能根据查询条件导出csv数据啊，我还是回答，能，能，能，重要事情说3遍！
写存储过程啊！啊，你不会写，好吧，我好人当到底，给你，给你，都给你！！
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:张述飞
-- Create date: 2016-04-13
-- Description: 根据输入的查询条件导出csv文件
-- =============================================
create proc [dbo].[test]
@condition nvarchar(500)
as
declare @sql nvarchar(1000),
@result int
begin
SET NOCOUNT ON;
--允许配置高级选项
exec sp_configure 'show advanced options','1';
--重新配置
reconfigure;
--启用xp_cmdshell
exec sp_configure 'xp_cmdshell','1'
reconfigure;
set @sql = 'bcp "select ''用户名'',''密码'' union all '+
'select username,'+
'(case when len(password)=0 then ''=char(32)'' else password end) as password '+
'from 数据库名.dbo.用户表名 where id > 0 '+@condition+'" queryout c:test.csv -t"," -q -c -T'
--print @sql
exec @result = master..xp_cmdshell @sql,no_output
--允许配置高级选项
exec sp_configure 'show advanced options','1';
--重新配置
reconfigure;
--关闭xp_cmdshell
exec sp_configure 'xp_cmdshell','0';
reconfigure;
select @result as failrows
end
创建完了存储过程，我们测试一下
declare @condition nvarchar(500)
set @condition=' and username like ''zhang%'''
exec test @condition
打印结果为0，说明导出成功了！
基本上我就写到这里了，至于程序于怎么调用，大家多动手试试就行了，^_^
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
