SQL Server分类导出数据.doc

SQL Server分类导出数据摘要： 本文对sql serve数据库中的数据分类导出至excel文件进行了研究，通过xp_cmdshell存储过程与bcp的分析与使用，提出了简单有效的解决办法。关键字：bcp;分类导出;queryout;sql server引言在实际应用中, 常常需要将sql serve数据库中的数据导出至excel中。将表中全部数据、按条件将部分数据导出至excel非常简单，但要将sql server表中数据按条件分类批量导出为多个excel文件就不那么简单了。例如：有一学生表（学号，姓名，系名，班级名，课程名称，成绩），现要按学号分类导出数据，每个学生为一类生成一个xls文件，若全校有一万三千名学生，就要分一万三千类，并生成一万三千个xls文件。如果我们一个个查询导出，工作量巨大，效率也低。为此，我们今天将研究bcp导出方法，采用while循环查询解决此问题。2、准备工作bcp是sql server中负责导入导出数据的一个命令行工具，它是基于db-library的，并且能以并行的方式高效地导入导出大批量的数据。此次研究，我们将调用sql server的一个系统存储过程xp_cmdshell以sql语句的方式运行bcp。bcp由四个动作组成，in为导入，out为导出，queryout为使用sql语句导出，format为导出格式文件。在这里，我们的数据源是sql语句，故使用queryout导出。扩展存储过程xp_cmdshell是一个功能非常强大的扩展存贮过程，可以让系统管理员以操作系统命令行解释器的方式执行给定的命令字符串。一般情况下，管理员会将xp_cmdshell禁用。在这里，我们先要将xp_cmdshell启用： use master exec sp_addextendedproc nxp_cmdshell， nxplog70.dll go3、具体实现使用bcp命令导出的语法格式为：bcp ”查询命令” queryout “xls文件保存路径”cu “登录名” p “密码”在命令窗口输入以下的命令：-定义两个变量，sql用来保存要运行的bcp命令，xue用来保存用来分类的学号declare sql varchar(600),xue int-xue取得最小的学号select xue=min(学号) from stud.dbo.学生表-如果学生表中存在此学号的学生时进行如下处理while exists(select * from 学生表 where 学号=xue)begin-先在xls文件中添加列名set sql=bcp “select +学号+ as 学号,+姓名+ as 姓名,+系名+ as 系名,+班级名+ as 班级名,+课程名称+ as 课程名称,+成绩+ as 成绩-使用联合查询将满足条件的记录（该学号对应学生的信息）也添加至xls文件中set sql=sql+ union all select 学号,姓名,系名,班级名,课程名称,成绩 from stud.dbo.学生表 where 学号=+cast(xue as varchar(8)+” queryout “f:+cast(xue as varchar(8)+.xls” -c -u “sa” -p “123”-调用xp_cmdshell运行bcp命令exec master.xp_cmdshell sql-获取下一个学生的学号赋值给xueselect xue=isnull(min(学号),xue+1) from stud.dbo.学生表 where 学号=xue+1end在书写命令时应注意以下几点：表名要使用“数据库名.所有者名.表名”的形式，否则会出错。所以，命令中的“学生表”正确的写法为“stud.dbo.学生表”。要区分大小写。-c为小写，-u为大写，-p 为大写。xls文件保存路径、查询命令这两项必须用双引号括起来。所有特殊符号都必须在英文状态下输入。bcp导出数据是不带字段名的，为了将字段名也导出至excel中，并且能正确导出，必须将要导出的字段全部转为字符型，否则即使你的命令和上面的一模一样，也会出现如下错误。最终，每个学号对应一个xls文件，运行结果如下图所示：打开“70801.xls”文件，结果如下图所示：4、结束语将sql server表中数据分类批量导出为多个excel文件是具有实际意义的，在平常的操作中使用广泛。如超