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，说明导出成功了！

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