使用SqlBulkCopy做資料轉檔

bcp( sqlbulkcopy ) 原本是命令列上的工具,用來做大量複製資料的工作。
在.NET裡面也可以調用他的功能。

SqlBulkCopy 類別可用於僅將資料寫入 SQL Server 資料表。 但是資料來源不僅限於 SQL Server;可使用任何資料來源,只要該資料可載入 DataTable 執行個體,或可使用 IDataReader 執行個體進行讀取。 M$ docs 

這次我們使用他來做資料表的轉檔,將會有準備資料與執行兩部分。

資料準備

SqlBulkCopy 可以接受DataRow[]、DataTable、IDataReader 做為資料來源。
這邊使用IDataReader,好處是較省記憶體,只會儲存目前的游標,使用DataTable 會在記憶體中儲存所有的資料,會有記憶體方面的問題。

 DataTable dataTable = new DataTable(); IDataReader reader = sourceConnection.ExecuteReader($@"...");

不過若是來源與目的的表有出入,仍然可以使用DataTable來存放資料。
例如我需要新增一個extraColumn

DataTable dataTable = new DataTable();
dataTable.Load(reader);
DataColumn dataColumn = new DataColumn("extraColumn", typeof(Int32));
dataTable.Columns.Add(dataColumn);
SqlBulkCopy.ColumnMappings.Add("extraColumn", "extraColumn");

上面例子是DataTable 由 IDataReader 載入後,再加入新的欄位與設定其型別,SqlBulkCopy也增加一筆對應規則。

執行

 
SqlBulkCopy sqlBulk = new SqlBulkCopy(targetConnection, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity, trans)
{
	DestinationTableName = targetTable,
};

sqlBulk.WriteToServer(reader);

SqlBulkCopyOptions 可以調整在做Copy時的行為,KeepNulls可以在目的端的值為NULL時不填入預設值,若不設定則在值為null時帶入預設值。
KeepIdentity可以保留來源的PK值,若不設定則由目的端的規則產生。

還有其他選項可以參考:MSDN

 

發表迴響

你的電子郵件位址並不會被公開。