`

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,说明导出成功了!

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



分享到:
评论

相关推荐

    bcp—SQLServer命令行数据导入导出工具使用总结.txt

    bcp是微软SQLServer数据库系统的命令行工具,用于进行大数据量的导入导出,该工具简单实用、效率极高,本文总结了bcp命令操作的各种设置开关功能含义和使用方法,可以为需要的用户提供有益帮助。

    sql server 中 BCP使用方法详解

    BCP(Bulk Copy Program)是 SQL Server 中负责导入导出数据的一个命令行工具,它是基于 DB-Library 的,并且能以并行的方式高效地导入导出大批量的数据。BCP 可以将数据库的表或视图直接导出,也能通过 SELECT FROM...

    SQL_Server数据导入导出工具BCP详解

    SQL Server的BCP(Bulk Copy Program)工具是一个强大的命令行实用程序,用于高效地导入大量数据到SQL Server数据库,或导出数据到文件。在本文中,我们将深入探讨BCP的工作原理、使用方法以及相关最佳实践,以帮助...

    sqlserver bcp导出到excel

    在SQL Server中使用BCP导出数据到Excel的具体方法通常涉及到以下几个关键步骤: 1. **使用BCP命令行参数**:BCP命令提供了多种选项来控制数据的导出过程。例如: - `-S server_name[\instance_name]` 指定目标SQL ...

    SQL Server三种导入导出数据方式比较

    在本文中,我们将详细比较 SQL Server 中的三种导入导出数据方式:使用 Transact-SQL 对数据进行处理;调用命令行工具 bcp 处理数据;使用数据转换服务(DTS)对数据进行处理。每种方式都有其特点和优缺点,我们将...

    sql server 通过BCP导出几张表内容

    可以通过批处理 定期导出数据库里面表的系信息

    SQL SERVER自动导出Word数据库文档

    6. **PowerShell**:另一种方法是使用PowerShell,它提供了丰富的SQL Server模块,可以调用SQL命令行工具(如sqlcmd或bcp)来导出数据,然后通过PowerShell脚本控制Word文档的操作。 7. **Third-party Tools**:...

    SQL Server表数据导出成Insert语句的工具

    2. **SQL Server的数据导出方式**:SQL Server提供了多种数据导出方式,如SQL Server Management Studio (SSMS) 的“任务”->“导出数据”,以及bcp命令行工具。这些方法通常用于导出整个数据库或表的数据,但不直接...

    SQLServer数据库表中数据导出成SQL语句工具

    在SQL Server数据库管理中,有时候我们需要将数据库表中的数据导出为SQL语句,以便于备份、迁移或者在其他环境中重建相同的数据结构和内容。针对这个需求,存在一种名为“SQLServer数据库表中数据导出成SQL语句工具...

    Sql Server数据库BCP导入导出工具

    这是一个数据初始化工具,数据导出:在ServerDbConfig.cfg文件里配置好数据库访问所需要的信息,在sql文件下配置好要导出的SqlFiles,执行批处理DBUpdator_Normal.bat,就可以获得导出数据的文本形式,同样的配置导入...

    BCP命令导入导出数据

    BCP 命令导入导出数据 BCP(Bulk Copy Program)是一种命令行工具,用于将数据...BCP 命令是一种强大的命令行工具,用于批量导入或导出数据到 SQL Server 数据库中。通过使用 BCP 命令,可以快速高效地处理大量数据。

    SQL Server中导入导出数据三方法比较

    在SQL Server中,数据导入导出是常见的数据库管理任务,涉及到数据迁移、备份或整合等多个场景。本篇文章将探讨三种主要方法:Transact-SQL、BCP命令行工具以及数据转换服务(DTS),并分析它们的特点和适用场景。 1....

    导出sqlserver 2000的表结构

    除了SQL查询,还可以使用SQL Server提供的扩展存储过程`xp_cmdshell`结合BCP命令来导出数据和表结构。BCP(Bulk Copy Program)是SQL Server的一个工具,用于快速导入导出大量数据。 #### 2. BCP命令解析 - **`...

    EXCEL与SQL SERVER 的导入与导出

    首先,Excel到SQL Server的导出通常用于将Excel中的数据批量导入到数据库中。这在数据分析、报表生成或者系统初始化时非常有用。导出过程涉及的关键步骤包括连接到SQL Server数据库,创建目标表(如果不存在),然后...

    第三讲 SQL Server2000数据导入导出

    DTS是SQL Server 2000中的一个工具,允许用户通过图形界面来导入和导出数据。你可以通过“开始”->“程序”->“Microsoft SQL Server”->“导入和导出数据”来启动它。该向导支持多种数据源,如Access、Excel和文本...

    SQLServer导出为Insert语句

    在SQL Server中,可以使用多种工具或T-SQL语句来导出数据,例如`bcp`命令行工具,`SELECT INTO`语句,或者使用SSMS(SQL Server Management Studio)的“任务”>“生成脚本”功能。 4. **Insert语句**:在SQL中,...

    SQL SERVER 数据的导入导出

    1. **SQL Server Management Studio (SSMS)**:这是最常用的数据导出工具,通过任务(Tasks) -> 导出数据(Export Data),可以选择不同的数据源和目标,支持多种格式如Excel、CSV、文本文件等。你可以设置数据转换规则...

Global site tag (gtag.js) - Google Analytics