作者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