sqlserver中的bcp查询导出海量数据

前几天,公司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打开方便操作。

有人说了，能不能加上汉字列名呢，绝对可以，如下写法

 

EXEC master..xp_cmdshell 'bcp " select ''用户名''，''密码'' union all

select username,password from 数据库名.dbo.表名" queryout c:\test.csv -c -t, -T'

又有人说了，如果导出的数据中有为空的话，csv文件会被截断，显示不全数据啊，没关系，假设密码有为空的用户，我们写成char(32)，这个表示一个空格，再用excel打开csv文件时就不会显示不全数据，这是一个没有办法的办法，如果大家有好的方法，请贴出来告诉我一声，谢谢了！如下写法

 

 

EXEC master..xp_cmdshell 'bcp " select ''用户名''，''密码'' union all

select username,case when len(password)=0 then ''=char(32)'' else password end as password from 数据库名.dbo.表名" queryout c:\test.csv -c -t, -T'

 

还有人说了，我能不能根据查询条件导出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，说明导出成功了！

基本上我就写到这里了，至于程序于怎么调用，大家多动手试试就行了，^_^