作者TeemingVoid (TeemingVoid)
看板Database
标题Re: [SQL ] 将SQL资料转成txt档?
时间Mon Aug 13 21:30:18 2012
原文恕删，谨简单整理您问题的重点:
1. 来源资料档的内容与汇入目的资料表的内容部分重复。
2. 由於每次汇入汇出的资料笔数有十万笔，「先删除後新增」的作法效率不彰。
3. 解决方案希望能在 SQL Server 与 Oracle 两种平台都能执行。
针对以上三点，我建议的解决方案是:
1. C# 程式读入文字档，利用 SqlBulkCopy 物件将资料整批汇入暂存资料表。
(SqlBulkCopy 的速度不输 bcp，而且，对象也不限 SQL Server)
2. 以 MERGE 指令，将暂存资料表合并到真正的目的资料表。
(SQL Server 与 Oracle 都支援 MERGE 指令)
有兴趣的网友，不妨一同试试以下这个 Lab: (我以 SQL Server 举例)
1. 在 SQL Server 建立 Lab 环境:
create database BulkTestDB
go
use BulkTestDB
go
create table BulkTestTable
(
id int primary key,
aDate date,
data varchar(15)
)
go
select * into BulkTestTempTable from BulkTestTable where 1 = 0
go
2. 利用 C# Express 新增一个 Windows 专案
3. 放置一个按钮，名称: btnCreateDataFiles，其 Click 事件如下:
using System.IO;
using System.Data.SqlClient;
...
// 在 C:\temp 建立两个文字档，等一下要汇入这两个文字档
private void btnCreateDataFiles_Click(object sender, EventArgs e)
{
int iRowCount = 250000; // 实验的笔数请自己调整
System.IO.Directory.CreateDirectory(@"c:\temp");
DateTime dNow = DateTime.Now;
StreamWriter w = new StreamWriter(@"c:\temp\dataSource1.txt", false);
for (int iRow = 1; iRow <= iRowCount; iRow += 2)
{
// format: #, Date, data#
w.WriteLine(string.Format("{0},{1},data{2}",
iRow, dNow.AddSeconds(iRow).ToString("yyyy-MM-dd"), iRow));
}
w.Close();
w = new StreamWriter(@"c:\temp\dataSource2.txt", false);
for (int iRow = 1; iRow <= iRowCount; iRow++)
{
// format: #, Date, DATA#
w.WriteLine(string.Format("{0},{1},DATA{2}",
iRow, dNow.AddSeconds(iRow).ToString("yyyy-MM-dd"), iRow));
}
w.Close();
btnCreateDataFiles.Text = "Done";
}
4. 放置一个按钮，名称: btnInsertOneByOne，Click事件及相关函式如下:
(汇入 dataSource1.txt，这个方法非常慢...)
// 建立并传回 Connection 物件
SqlConnection GetConnection()
{
string s =
"Server=.\\SQLExpress;Database=BulkTestDB;Integrated Security=true";
SqlConnection Result = new SqlConnection(s);
Result.Open();
return Result;
}
// 这个方法很慢，非常慢 _.,-zZ
private void btnInsertOneByOne_Click(object sender, EventArgs e)
{
SqlConnection cn = GetConnection();
SqlCommand cmd =
new SqlCommand("Insert into BulkTestTable values (@id, @aDate, @data)", cn);
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@aDate", SqlDbType.Date);
cmd.Parameters.Add("@data", SqlDbType.VarChar, 15);
StreamReader r = new StreamReader(@"c:\temp\dataSource1.txt");
while (!r.EndOfStream)
{
string[] Data = r.ReadLine().Split(',');
for (int iCol = 0; iCol < Data.Length; iCol++)
cmd.Parameters[iCol].Value = Data[iCol];
cmd.ExecuteNonQuery();
}
r.Close();
cn.Close();
btnInsertOneByOne.Text = "Done";
}
5. 放置一个按钮，名称: btnBulkCopyToTempTable，其 Click 事件:
(将 dataSource2.txt 以 SqlBulkCopy 整批汇入到暂存资料表)
(dataSource2.txt 比 dataSource1.txt 大上一倍，但汇入速度至少快二十倍)
private void btnBulkCopyToTempTable_Click(object sender, EventArgs e)
{
SqlConnection cn = GetConnection();
SqlBulkCopy sbc = new SqlBulkCopy(cn); // 1. 建立SqlBulkCopy物件
sbc.DestinationTableName = "BulkTestTempTable"; // 2. 指定目的资料表
DataTable dt = new DataTable();
dt.Columns.Add("id", System.Type.GetType("System.Int32"));
dt.Columns.Add("aDate", System.Type.GetType("System.DateTime"));
dt.Columns.Add("data", System.Type.GetType("System.String"));
StreamReader r = new StreamReader(@"c:\temp\dataSource2.txt");
while (!r.EndOfStream)
{
string[] Data = r.ReadLine().Split(',');
dt.Rows.Add(Data);
}
r.Close();
sbc.WriteToServer(dt); // 3.整批写到资料库伺服器
sbc.Close();
cn.Close();
btnBulkCopyToTempTable.Text = "Done";
}
6. 放置一个按钮，名称: btnMerge，其 Click 事件如下:
private void btnMerge_Click(object sender, EventArgs e)
{
SqlConnection cn = GetConnection();
string sMergeCommand =
"merge BulkTestTable as dest" +
" using (select * from BulkTestTempTable) as source" +
" on (dest.id = source.id)" +
" when matched then " +
" update set dest.data = source.data" +
" when not matched by target then " +
" insert (id, aDate, data) values (source.id, source.aDate, source.data);";
SqlCommand cmd = new SqlCommand(sMergeCommand, cn);
cmd.ExecuteNonQuery();
cn.Close();
btnMerge.Text = "Done";
}
以上，请依序执行上述按钮。
关於下列 MERGE 叙述，速度的关键在於 on 比对条件，如果没有编索引，速度快不
起来，详情请参考:
http://ppt.cc/L84i
merge BulkTestTable as dest
using (select * from BulkTestTempTable) as source
on (dest.id = source.id)
when matched then
update set dest.data = source.data
when not matched by target then
insert (id, aDate, data) values (source.id, source.aDate, source.data);
最後，SQL Server 的 MERGE 叙述也可以直接读入文字档，不一定要透过 C# 程式先
将资料读入暂存资料表，详细作法在上述
http://ppt.cc/L84i 文件中也有提到。预祝
顺利成功! ^_^
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 118.170.133.215
※ 编辑: TeemingVoid 来自: 118.170.133.215 (08/13 21:38)
1F：推 soon:太感人了Q_Q 不仅整理了我杂乱的需求还提供范例，感谢您！ 08/14 12:44