Question

SQL2012 全部数据太大 怎么导出到多个文件

看到你回复这个问题的， 但是你最后说私信， 都不知道你说什么，能告诉下我吗？ 我现在数据库都107W了
因为是需要把数据导出来， 如果直接使用‘导出数据’，结果导出到6W多就提示失败了，
导出文件为csv或者xls都可以(2003打开)
每6万条数据一个文件
求大神指点，多谢。在线等。。。

Answer 1

/*
--用BCP试试,不行再用下面的存储过程
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.Apo_village"
queryout "c:/Apo_SFZ.xlsx" -c -S"服务器" -U"sa" -P"密码"'
*/

--这是用C#写的存储过程,不知道你会不会编译到SQL Server
--在数据库这样调用就是了
--Exec BulkCopyToXls 'SQL查询语句','路径','文件名',最大记录数
--Exec BulkCopyToXls 'select * from 表','G:\Test','Table',60000
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class myProcedure
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void BulkCopyToXls(SqlString sql, SqlString savePath, SqlString tableName, SqlInt32 maxRecordCount)
{
if (sql.IsNull || savePath.IsNull || tableName.IsNull)
{
SqlContext.Pipe.Send(" 输入信息不完整！");
}
//每个excel文件最大容纳65534
ushort _maxRecordCount = ushort.MaxValue - 1;
if (maxRecordCount.IsNull == false && maxRecordCount.Value < ushort.MaxValue && maxRecordCount.Value > 0)
_maxRecordCount = (ushort)maxRecordCount.Value;
ExportXls(sql.Value, savePath.Value, tableName.Value, _maxRecordCount);
}
private static void ExportXls(string sql, string savePath, string tableName, System.UInt16 maxRecordCount)
{
//创建文件路径
if (System.IO.Directory.Exists(savePath) == false)
{
System.IO.Directory.CreateDirectory(savePath);
}
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = sql;
using (SqlDataReader reader = command.ExecuteReader())
{
int i = 0;
int totalCount = 0;
int tick = System.Environment.TickCount;
SqlContext.Pipe.Send(" 开始导出数据");
while (true)
{
string fileName = string.Format(@"{0}/{1}.{2}.xls", savePath, tableName, i++);
int iExp = Write(reader, maxRecordCount, fileName);
long size = new System.IO.FileInfo(fileName).Length;
totalCount += iExp;
SqlContext.Pipe.Send(string.Format(" 文件{0}, 共{1} 条, 大小{2} 字节", fileName, iExp, size.ToString("###,###")));
if (iExp < maxRecordCount) break;
}
tick = System.Environment.TickCount - tick;
SqlContext.Pipe.Send(" 导出数据完成");
SqlContext.Pipe.Send("-------");
SqlContext.Pipe.Send(string.Format(" 共{0} 条数据，耗时{1}ms", totalCount, tick));
}
}
}
}

private static void WriteObject(ExcelWriter writer, object obj, System.UInt16 x, System.UInt16 y)
{
//判断写数字还是写字符
string type = obj.GetType().Name.ToString();
switch (type)
{
case "SqlBoolean":
case "SqlByte":
case "SqlDecimal":
case "SqlDouble":
case "SqlInt16":
case "SqlInt32":
case "SqlInt64":
case "SqlMoney":
case "SqlSingle":
if (obj.ToString().ToLower() == "null")
writer.WriteString(x, y, obj.ToString());
else
writer.WriteNumber(x, y, Convert.ToDouble(obj.ToString()));
break;
default:
writer.WriteString(x, y, obj.ToString());
break;
}
}

private static int Write(SqlDataReader reader, System.UInt16 count, string fileName)
{
int iExp = count;
ExcelWriter writer = new ExcelWriter(fileName);
writer.BeginWrite();
//写字段信息
for (System.UInt16 j = 0; j < reader.FieldCount; j++)
{
writer.WriteString(0, j, reader.GetName(j));
}
//循环一行一行读入数据
for (System.UInt16 i = 1; i <= count; i++)
{
if (reader.Read() == false)
{
iExp = i - 1;
break;
}
//循环一格一格写入数据
for (System.UInt16 j = 0; j < reader.FieldCount; j++)
{
WriteObject(writer, reader.GetSqlValue(j), i, j);
}
}
writer.EndWrite();
return iExp;
}

public class ExcelWriter
{
System.IO.FileStream _wirter;
//创建文件
public ExcelWriter(string strPath)
{
_wirter = new System.IO.FileStream(strPath, System.IO.FileMode.OpenOrCreate);
}

//写数组
private void _writeFile(System.UInt16[] values)
{
foreach (System.UInt16 v in values)
{
byte[] b = System.BitConverter.GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}

//写文件头
public void BeginWrite()
{
_writeFile(new System.UInt16[] { 0x809, 8, 0, 0x10, 0, 0 });
}
//文件尾
public void EndWrite()
{
_writeFile(new System.UInt16[] { 0xa, 0 });
_wirter.Close();
}
//写数字到单元格
public void WriteNumber(System.UInt16 x, System.UInt16 y, double value)
{
_writeFile(new System.UInt16[] { 0x203, 14, x, y, 0 });
byte[] b = System.BitConverter.GetBytes(value);
_wirter.Write(b, 0, b.Length);
}
//写字符到单元格
public void WriteString(System.UInt16 x, System.UInt16 y, string value)
{
byte[] b = System.Text.Encoding.Default.GetBytes(value);
_writeFile(new System.UInt16[] { 0x204, (System.UInt16)(b.Length + 8), x, y, 0, (System.UInt16)b.Length });
_wirter.Write(b, 0, b.Length);
}
}
};

追问

我看看