說到SQLServer 的數據批量導入，下面用常用的幾種方式做下對比，後面詳細介紹每種方式如何使用（本文的代碼使用語言c#）
導入方式
是否需寫代碼
導入過程能否對數據加工
插入數據的速度
多表數據導入
是否必需寫SQL語句
1.通過SQLServer客戶端管理工具
F
F
快
F
F
2.循環調用插入語句、或存儲過程
T
T
慢
T
T
3.使用SqlBulkCopy
T
F
快
F
F
4.使用SQLServer表值參數
T
T
快
T
T
1.通過SQLServer客戶端管理工具
打開SQLServer客戶端連接要操作的數據庫引擎
右鍵要操作的數據庫，選擇任務--導入數據，第壹次使用會彈出向導頁如下圖：
點下壹步，壹般要導入的數據都是excel，數據源我們選擇Microsofy Excel(不同版本會有些差異)，
瀏覽選擇要導入的excel文件；
下壹步選擇目標數據源選擇我們的SQLServer
根據需要壹直點下壹步，需要註意在選擇表和數據源頁面，根據實際需要選擇對應的表，以及編輯列的映射,
最後點擊完成，導入數據。
2.循環調用插入語句、或存儲過程
此方法就是調用寫好的sql語句或存儲過程來循環的插入數據導數據庫；根據需要可以在讀取文件數據後，對數據進行校驗和加工。
下面代碼是壹個循環插入的實現，如果需要輸出導入進度可以用BackgroundWorker+ progressBar在頁面上顯示導入進度；
private int DataImport()
{
if (File.Exists(path))
File.Delete(path);
int i = 0;
// 打開數據庫連接
string strConn = System.Configuration.ConfigurationManager.AppSettings["SsConnString"];
SqlConnection Coon = new SqlConnection(strConn);
if (Coon.State.Equals(ConnectionState.Closed))
{
Coon.Open();
}
foreach (DataRow dr in m_dt.Rows)
{
i++;
if (bkWorker.CancellationPending)
{
e.Cancel = true;
return -1;
}
SqlParameter[] parms = {
new SqlParameter("@Name", SqlDbType.VarChar, 32),
new SqlParameter("@Sheng", SqlDbType.VarChar),
new SqlParameter("@City", SqlDbType.VarChar),
new SqlParameter("@Xian", SqlDbType.VarChar)
};
try
{
parms[0].Value = (dr["姓名"] + "").Trim();
parms[7].Value = dr["省"] + "";
parms[8].Value = dr["市"] + "";
parms[9].Value = dr["縣"] + "";
}
catch (Exception)
{
MessageBox.Show("確保數據表中的列名和模版中的壹致！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return -1;
}
try
{
SqlCommand Cmd = Tools.CreateCmd("P_Data_Import", parms, Conn);
int iRet = Cmd.ExecuteNonQuery();
if (iRet == 0)
{
continue;
}
}
catch (Exception ex)
{
Tools.Log_Write("第" + (m_dt.Rows.IndexOf(dr)+1).ToString() + "行導入出錯：" + ex.Message, "d:\\數據導入日誌.txt");
continue;
}
}
if (MessageBox.Show("數據導入完成！,打開導入日誌！", "提示") == DialogResult.OK)
{
this.buttonImport.Enabled = true;
if (File.Exists(path))
System.Diagnostics.Process.Start(path);
}
Conn.Close();
return -1;
}
// 打開數據庫連接
public static SqlConnection ReturnConn()
{
string strConn = "server=數據庫地址;uid=數據庫賬號;pwd=密碼;database=數據庫名"
SqlConnection Coon = new SqlConnection(strConn);
if (Coon.State.Equals(ConnectionState.Closed))
{
Coon.Open();
}
return Coon;
}
// 執行帶參數的存儲過程
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlConnection Conn)
{
SqlConnection SqlConn = Conn;
if (SqlConn.State.Equals(ConnectionState.Closed))
{
SqlConn.Open();
}
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = SqlConn;
Cmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}
3.使用SqlBulkCopy
下面以導入學生消課數據為例，導入數據的方法，關於SqlBulkCopy(官方解釋：允許妳使用其他源的數據有效地批量加載 SQL Server 表。)的使用可以到到網上搜索，資料壹大堆，官方文檔直通通車
首先要構造要導入數據格式的DataTable類型的對象（TransferData）、其次要設置和數據源的列映射關系
Stopwatch 用於計算導入數據耗費時間
private void InsertTwo()
{
OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() != DialogResult.OK)
{
return;
}
Stopwatch sw = new Stopwatch();
//給datatable 構造Column
DataTable dt = Tools.TransferData(fd.FileName, "sheet1","");
dt.Columns.Add(xhFee);
this.btn_Import.Enabled = false;
string strConn = System.Configuration.ConfigurationManager.AppSettings["SsConnString"];
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = "T_FI_IncomeDetail";
bulkCopy.BatchSize = dt.Rows.Count;
//設置列對應關系
bulkCopy.ColumnMappings.Add("輔導類型", "FdId");
bulkCopy.ColumnMappings.Add("消耗課時或課次", "XhKeshi");
bulkCopy.ColumnMappings.Add("消耗日期", "CreateTime");
bulkCopy.ColumnMappings.Add("學生姓名", "Name");
conn.Open();
sw.Start();
int totalRow = dt.Rows.Count;
if (dt != null && dt.Rows.Count != 0)
{
dateTimeDelete.Value = Convert.ToDateTime(dt.Rows[0]["消耗日期"]);
bulkCopy.WriteToServer(dt);
sw.Stop();
}
MessageBox.Show(string.Format("插入{0}條記錄***花費{1}毫秒", totalRow, sw.ElapsedMilliseconds));
}
}
// 獲取excel數據並填充到DataTable
public static TransferData(string excelFile, string sheetName,string strScreen)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
//獲取全部數據
string strConn = "Provider=Microsoft.Ace.Oledb.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter myCommand = null;
myCommand = new OleDbDataAdapter("Select * from [Sheet1$] " + strScreen, strConn);
myCommand.Fill(dt);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return dt;
}
4.使用SQLServer表值參數
該方法對於需要批量導入數據，有需要對數據進行邏輯操作，影響多張表時，尤其實用；本例以導入用戶資料為例，親測由於邏輯復雜在存儲過程中使用遊標處理2000條數據，2s就可完全導入。
主要是應用了SQLServer的表類型參數，通過給存儲過程傳入表數據，讓sql操作都在數據庫中進行，提升操作性能。
首先要根據要在數據庫 創建自定義表類型，創建語句格式如下：
CREATE TYPE [dbo].[UserInfo] AS TABLE(
[Name] [varchar](32) NULL,
[Code] [varchar](32) NULL,
[Gender] [varchar](32) NULL,
[Birthday] [datetime] NULL
)
存儲過程使用方式：UserInfo即為提前創建好的自定義表類型
create proc procName
(
@DataTable UserInfo readonly
)
as
begin
-- 實現自己的邏輯對@DataTable的使用可以向普通表壹樣，
-- 建議 如果需要對@DataTable需要連表過濾數據，請使用臨時表， 否則可能會提示 超出數據庫設置的最大查詢時間
--（在數據庫引擎，右鍵屬性--連接中可以查看使用查詢調控器防止查詢時間過長，不勾選默認30s），
-- 建議邏輯操作能放在代碼中處理的，不要放在存儲過程中，為了減小數據庫服務器壓力
end
代碼調用實例：
private int DataImport1(object sender)
{
Stopwatch sw = new Stopwatch();
sw.Start();
string path = "d:\\數據導入日誌.txt";
if (File.Exists(path))
File.Delete(path);
int count = m_dt.Rows.Count;
SqlConnection Conn = SsZongs.ReturnConn();
DataTable dt = new DataTable("userIfo");
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Code", typeof(string));
dt.Columns.Add("Gender", typeof(string));
dt.Columns.Add("Birthday", typeof(DateTime));
try
{
for (int i = 0; i < count; i++)
{
try
{
dt.Rows.Add((m_dt.Rows[i]["姓名"] + "").Trim(),
m_dt.Rows[i]["編號"].ToString(),
m_dt.Rows[i]["性別"].ToString(),
m_dt.Rows[i]["出生日期"]
);
}
catch (Exception)
{
MessageBox.Show("確保數據表中的列名和模版中的壹致！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return -1;
}
}
Tools.insertTableToDB(dt, "procName");
}
catch (Exception ex)
{
Tools.Log_Write(ex.Message, "d:\\數據導入日誌.txt");
}
finally
{
sw.Stop();
if (MessageBox.Show("數據導入完成！耗時"+ sw.ElapsedMilliseconds + "毫秒,打開導入日誌！", "提示") == DialogResult.OK)
{
this.buttonImport.Enabled = true;
if (File.Exists(path))
System.Diagnostics.Process.Start(path);
}
}
return -1;
}
public static void insertTableToDB(System.Data.DataTable dt,string procName)
{
SqlConnection sqlCon = SsZongs.ReturnConn();
using (var cmd = new SqlCommand(procName, sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter("@DataTable", SqlDbType.Structured) { Value = dt };
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
sqlCon.Close();
}
總結
以上幾種方式，我在實際工作中都有使用， 具體業務還需要根據情況選擇合適的方案。
文檔編寫能力不好，有需要的可以隨時交流。
我的掘金
SQLServer數據批量導入的幾種方式
標簽：elfuse數據庫服務器blebatchtail圖片ide資料