sql server查询数据并生成指定大小文件的方法

--调用管理权限
use master
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE

--生成数据文件
declare @sqlstr varchar(3000)
declare @i int
declare @count int

select identity(int,1,1) as id ,email
into testdb.dbo.usersemail
from testdb.dbo.reguser where (year(jointime)='2012') and (email is not null)

set @i=0
set @count= (select COUNT(1) from testdb.dbo.reguser where (year(jointime)='2012') and (email is not null))
while @i*10000<@count
begin
set @sqlstr='bcp "select top 10000 email from testdb.dbo.usersemail  where id>='+cast(@i*10000 as varchar(10))+' and id<'+cast(@i*10000+10000 as varchar(10))+'" queryout "d:2012'+cast(@i as varchar(10))+'.txt" -c -T'
exec master.dbo.xp_cmdshell @sqlstr
set @i=@i+1
end

--删除临时表
drop table testdb.dbo.usersemail