| 本文實作在c#中可高效的将excel資料導入到sqlserver資料庫中,通過循環來拼接sql，這樣做不但容易出錯而且效率低下，最好的辦法是使用bcp，也就是System.Data.SqlClient.SqlBulkCopy 類來實作。不但速度快，而且代碼簡單，下面測試代碼導入一個6萬多條資料的sheet， using System; using System.Data; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsApplication2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { //測試，将excel中的sheet1導入到sqlserver中 string connString = "server=localhost;uid=sa;pwd=sqlgis;database=master"; System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() == DialogResult.OK) { TransferData(fd.FileName, "sheet1", connString); } } public void TransferData(string excelFile, string sheetName, string connectionString) { DataSet ds = new DataSet(); try { //擷取全部資料 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); //如果目标表不存在則建立 string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName); foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string.Format("[{0}] varchar(255),", c.ColumnName); } strSql = strSql.Trim(',') + ")"; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp導入資料 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100;//每次傳輸的行數 bcp.NotifyAfter = 100;//進度提示的行數 bcp.DestinationTableName = sheetName;//目标表 bcp.WriteToServer(ds.Tables[0]); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } } //進度顯示 void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) { this.Text = e.RowsCopied.ToString(); this.Update(); } } } 上面的TransferData基本可以直接使用，如果要考慮周全的話，可以用oledb來擷取excel的表結構，并且加入ColumnMappings來設定對照字段，這樣效果就完全可以做到和sqlserver的dts相同的效果 =================================================================== using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.OleDb; using System.Configuration; using System.Data.SqlClient; namespace InExcelOutExcel { public partial class ExcelToDB : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { FileSvr fileSvr = new FileSvr(); System.Data.DataTable dt = fileSvr.GetExcelDatatable("C:\\Users\\NewSpring\\Desktop\\Demo\\InExcelOutExcel\\InExcelOutExcel\\excel\\ExcelToDB.xlsx", "mapTable"); fileSvr.InsetData(dt); } } class FileSvr { /// <summary> /// Excel資料導入Datable /// </summary> /// <param name="fileUrl"></param> /// <param name="table"></param> /// <returns></returns> public System.Data.DataTable GetExcelDatatable(string fileUrl, string table) { //office2007之前 僅支援.xls //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';"; //支援.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标題，不是資料； const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; System.Data.DataTable dt = null; //建立連接配接 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl)); try { //打開連接配接 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) { conn.Open(); } System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //擷取Excel的第一個Sheet名稱 string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); //查詢sheet中的資料 string strSql = "select * from [" + sheetName + "]"; OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); DataSet ds = new DataSet(); da.Fill(ds, table); dt = ds.Tables[0]; return dt; } catch (Exception exc) { throw exc; } finally { conn.Close(); conn.Dispose(); } } /// <summary> /// 從System.Data.DataTable導入資料到資料庫 /// </summary> /// <param name="dt"></param> /// <returns></returns> public int InsetData(System.Data.DataTable dt) { int i = 0; string lng = ""; string lat = ""; string offsetLNG = ""; string offsetLAT = ""; foreach (DataRow dr in dt.Rows) { lng = dr["LNG"].ToString().Trim(); lat = dr["LAT"].ToString().Trim(); offsetLNG = dr["OFFSET_LNG"].ToString().Trim(); offsetLAT = dr["OFFSET_LAT"].ToString().Trim(); //sw = string.IsNullOrEmpty(sw) ? "null" : sw; //kr = string.IsNullOrEmpty(kr) ? "null" : kr; string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values ('{0}','{1}',{2},{3})", lng, lat, offsetLNG, offsetLAT); string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString(); SqlConnection sqlConnection = new SqlConnection(strConnection); try { // SqlConnection sqlConnection = new SqlConnection(strConnection); sqlConnection.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandText = strSql; sqlCmd.Connection = sqlConnection; SqlDataReader sqlDataReader = sqlCmd.ExecuteReader(); i++; sqlDataReader.Close(); } catch (Exception ex) { throw ex; } finally { sqlConnection.Close(); } //if (opdb.ExcSQL(strSql)) // i++; } return i; } } }  假設有兩個表A,B，都隻有一個字段PHONE 1，MINUS SELECT PHONE FROM A MINUS SELECT PHONE FROM B; 相當于用結果集A減去結果B得出的結果集。同樣的效果也可以用SELECT PHONE FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.PHOEN=B.PHONE)來達到。 2，INTERSECT SELECT PHONE FROM A INTERSECT SELECT PHONE FROM B; 相當于求結果集A與結果集B的交集。 3，UNION SELECT PHONE FROM A UNION SELECT PHONE FROM B; 相當于求結果集A與結果集B的合集，去重。 4，UNION ALL SELECT PHONE FROM A UNION ALL SELECT PHONE FROM B; 相當于求結果集A與結果集B的合集，不去重。 3 執行個體演練 3.1建立表并插入資料 Create table A(A1 number(12),A2 varchar2(50)); Create table B(B1 number(12),B2 varchar2(50)); Insert Into A Values (1,'a'); Insert Into A Values (2,'ba'); Insert Into A Values (3,'ca'); Insert Into A Values (4,'da'); Insert Into B Values (1,'a'); Insert Into B Values (2,'bba'); Insert Into B Values (3,'ca'); Insert Into B Values (5,'dda'); Insert Into B Values (6,'Eda'); COMMIT; 3.2進行增量差異資料比較 3.2.1原始表A與比較表B的增量差異 Select * from A minus select * from B; 結果如下： A1 A2 --------------------------------------------------------------- 2 ba 4 da 3.2.2比較表B與原始表A的增量差異 Select * from B minus select * from A; 結果如下： B1 B2 --------------------------------------------------------------- 2 bba 5 dda 6 Eda 3.2.3兩種增量差異的合集 此合集包含3類資料： --1、原始表A存在、比較表B不存在，屬于删除類資料，出現次數1 --2、原始表A不存在、比較表B存在，屬于新增類資料，出現次數1 --3、原始表A和比較表B都存在，屬于修改類資料，出現次數2 Select A1,A2,1 t from (Select * from A minus select * from B) union Select B1,B2,2 t from (Select * from B minus select * from A); 結果如下： A1 A2 T ------------- -------------------------------------------------- ---------- 2 ba 1 2 bba 2 4 da 1 5 dda 2 6 Eda 2 3.3得到結果 Select A1,sum(t) from (Select A1,A2,1 t from (Select * from A minus select * from B) union Select B1,B2,2 t from (Select * from B minus select * from A)) Group by A1; 結果如下： A1 SUM(T) ----------------------- 6 2 2 3 4 1 5 2 結果中SUM(T)為1的為“删除”的資料，SUM(T)為2的為“新增”的資料，SUM(T)為3的為“修改”的資料。 方法二(通過Sql語句來比較)： 建立表的語句：
當中除id不為空，其它三項均可能為空。 一般情況下我們可以用
在oracle 情況下如果兩張表的某個字段都為null則其通過A.*=B.*是比較不出來的。 是以要額外加上這樣的判斷
通過上面這個sql語句我們就可以找在A表中存在，但在B表中不存在的資料 反過來可以查詢在B表中存在，但在A表中不存在的資料。 若兩個查詢都為空，則說明兩張表的相應字段完全相同。 若這兩張表在不同的資料庫，則要通過建立DBLink。 優點：資料比較快，特别是在資料量比較大的資料優勢更加明顯， 缺點：比較時不能很快看出兩張表的差異。因為查詢的隻是當中的某一張表。 select * from A minus select * from B; select * from B minus select * from A; 比較表結構相同的兩表之間的差異用minus。 不同的字段可以選擇對應的列來比較 如 select pno,cno from A minus select pno,cno from B; insert into i_itembom select bb.* from ( select item_code,mat_code from i_itembom_bak minus select item_code,mat_code from i_itembom) aa inner join i_itembom_bak bb on aa.item_code=bb.item_code and aa.mat_code=BB.MAT_CODE insert into i_itembom select bb.item_code,bb.mat_code,bb.qty_perunit,location,'','ERP',SYSDATE,bb.STATUS_MOD from (select item_code,mat_code from I_ERP_ITEM_BOM minus select item_code,mat_code from i_itembom) aa inner join I_ERP_ITEM_BOM bb on aa.item_code=bb.item_code and aa.mat_code=BB.MAT_CODE |
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)