[C#]使用Bulk Insert

2013-11-11

6473
0
SQL

[C#]使用Bulk Insert

紀錄一下過程

問題描述

之前去資料倉儲(資料庫為Teradata)讀取資料後產生Excel供使用者下載報表資料，發現連線狀況很不穩定

，經常會有timeout情況發生。

解決方式

之前有試過調整timeout時間及分批查詢後產生資料，效果並不好，研判原因如下：

1. Teradata為資料導向設計，對於OLAP查詢支援度及速度不佳。

2. 連結方式為ODBC，並非Native方式；速度受限。

3. 資料倉儲本身提供大量靜態報表，使用批次方式產生；本身就不適合使用即時查詢。

最後決議將將資料從資料倉儲(Teradata)回寫一份到網站的資料庫(Sql server)，步驟如下：

1. 每週定期將資料從資料倉儲(Teradata)回寫一份到網站的資料庫(Sql server)。

2. 每次回寫資料一定先全部清空表格後再新增。

3. 網站的資料庫表格定義與資料倉儲的查詢欄位數目及長度需一致。

4. Sql server的欄位定義均為nvarchar，減少型態轉換發生錯誤的情形。

Sql server表格欄位定義

程式碼

using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Text;

namespace ImportDataFromEntData
{
    /// <summary>
    /// 主程式
    /// 目的：從TeraData讀取資料回寫到SqlServer
    /// 作者：林大貓
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                DataTable entCust = new DataTable();
                entCust = GetQueryResultFromDw(GetAllDataFromDw());
                using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["testdb"].ToString()))
                {
                    dbConnection.Open();
                    string cmdText = "Delete from ENTCUST ";
                    using (SqlCommand cmd = new SqlCommand(cmdText, dbConnection))
                    {
                        Console.WriteLine("刪除資料筆數 : " + cmd.ExecuteNonQuery().ToString());
                    }
                    using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                    {
                        s.DestinationTableName = "ENTCUST";
                        s.WriteToServer(entCust);
                    }
                }
                Console.WriteLine("變更完畢!");
                //Console.Read();
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
        }
        /// <summary>
        /// 取得查詢結果
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        static DataTable GetQueryResultFromDw(string sqlCmd)
        {

            DataTable result = new DataTable();
            try
            {
                using (OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["TeraDataDW"].ToString()))
                {
                    using (OdbcDataAdapter da = new OdbcDataAdapter(sqlCmd, conn))
                    {
                        da.Fill(result);
                    }
                }
                return result;
            }
            catch (Exception k)
            {
                throw k;
            }
        }
        static string GetAllDataFromDw()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(@"SELECT A.PROD_TYPE");
            sb.AppendLine(@"  , A.PARTY_ID");
            sb.AppendLine(@"  , B.PARTY_NAME");
            sb.AppendLine(@"  , A.Work_YYYYMM");
            sb.AppendLine(@"  , A.LAST_12M");
            sb.AppendLine(@"  , A.LAST_11M");
            sb.AppendLine(@"  , A.LAST_10M");
            sb.AppendLine(@"  , A.LAST_09M");
            sb.AppendLine(@"  , A.LAST_08M");
            sb.AppendLine(@"  , A.LAST_07M");
            sb.AppendLine(@"  , A.LAST_06M");
            sb.AppendLine(@"  , A.LAST_05M");
            sb.AppendLine(@"  , A.LAST_04M");
            sb.AppendLine(@"  , A.LAST_03M");
            sb.AppendLine(@"  , A.LAST_02M");
            sb.AppendLine(@"  , A.LAST_01M");
            sb.AppendLine(@"");
            sb.AppendLine(@"FROM");
            sb.AppendLine(@" INTRA_VIEW.CUST_SAV_AVG_BAL_CORP AS A");
            sb.AppendLine(@" LEFT JOIN INTRA_VIEW.PARTY AS B");
            sb.AppendLine(@"  ON A.PARTY_ID = B.PARTY_ID");
            sb.AppendLine(@"ORDER BY");
            sb.AppendLine(@" A.PARTY_ID");
            return sb.ToString();
        }
    }
}

輸出結果

timeout不會再發生了。

參考資料

使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料(SQL Server)

資料倉儲Wiki

Bulk Insert into SQL Server using SqlBulkCopy in C#

回首頁