Lawrence Blog

一直以來，我很少遇到需要一次性大量寫入資料的需求，印象中只有幾次需要將備份的歷史數據大量的轉回正式環境，那次因為不是很緊急，所以就直接下 SQL 讓他慢慢地寫（老實說真他 ○ × 的久），但在某次機會下，我查到 MSSQL 本身有提供批次寫入的語法（不好意思直白的說自己 SQL 不太好 XD），可以快速寫入大量的數據，因此這篇文章我要記錄一下如何在 MSSQL 和 MariaDB 批次寫入大量的資料。 MSSQL 批次寫入 Bulk Insert 是 MSSQL 提供給大量寫入資料庫的一個好用的語法，適用於一次性寫入數十萬甚至數千萬筆的資料，其主要由底下兩個實體檔案所組成。 Format File（格式檔）：用來定義要寫入的數據結構。 資料文字檔：實際要導入的數據，例如 CSV 或 TXT 檔案。 Format File  SQL Server 支援兩種類型的格式檔案：非 XML 格式和 XML 格式。 非 XML 格式是舊版 SQL Server 所支援的原始格式，詳細的說明可參考 官網 ，這邊我就用非 XML 格式，也就是文字檔的格式來說明。

最近在查一個案例，使用者反應在查一個員工數比較少的請假資料的時候會出現錯誤畫面，查員工數較多的公司別的時候反而很快的就查詢出來了，當下一開始的直覺可能是資料面有問題，因此打開程式執行後發現該錯誤為ASP.NET畫面查詢SQL時等待過久timeout所導致，但這問題就來了，為何資料比較少查詢反而會timeout，資料較多的反而不會。 跟同事一起討論後大家一開始都覺的是Index的關係，因此就開始往錯誤的Index去查，結果查出了大學問(小弟我SQL方面的功力很破請見諒)。原來SQL Server在處理Join的時後，會跟據Join table的資料量、索引等…來決定使用不同的實體處理模式，SQL Server本身共有三種物理連接方式，【巢狀迴圈(Nested Loops)】、【合併聯結(Merge Join) 】、【雜湊比對(Hash Match)】。下面先簡單的說明三種連結的處理原則。

今日再寫一個批次匯入的作業，有個需求是當更新時間一樣的時候，要判斷欄位的大小來決定排序的方式，而我想要的排序方式是當正數時遞減排序，當負數的時候遞增排序。先看一下我的資料結構如下圖。

最近要調整公司原有系統機制的時候，由於資料架構的關係不得以必須使用到SQL來遞迴查詢一些資料，一開始我的想法本來是跟程式的觀念一下寫一個Function然後遞迴呼叫自己，但想來想去覺得這樣好像怪怪的，資料量大的時候怕會有效能問題，於是就上Google查了一下，果然有SQL本身就有提供類似遞迴的語法(SQL Server2005 + 的版本才支援)。

使用本機端查詢(A電腦)另外一台DataBase(B電腦)的時候，有兩種方法。 方法一在B電腦開啟遠端連線設定(這裡不解說如何設定，有興趣可參考我先前的 文章 )。 方法二使用LinkServer，但是最近有個Case就是使用方法二的時候發現在讀取B電腦自定義的 Function出現了【 訊息 4122，層級 16，狀態 1，行 1不允許遠端資料表值函數呼叫。 】 的錯 誤訊息，仔細查看了一下問題，可以發現我們在建LinkServer的時候SQL Server只幫我們建立了Table和View(如下圖)。

ROW_NUMBER() OVER ( [ <partition_by_clause> ] <order_by_clause> ) 傳回結果集資料分割內某資料列的序號，序號從 1 開始，每個資料分割第一個資料列的序。(說白話一點就是說幫每個查尋回來的資料列給予自動編號) 例如 : select row_number() over (order by fieldname asc) n umber,fieldname from tablename

CHARINDEX ( expression1 ,expression2 [ , start_location ] ) 搜尋 expression2 中的 expression1 ，並在找到時傳回它的開始位置。搜尋會在 start_location 開始。 引數 expression1 這是字元 expression ，其中包含要尋找的順序。 expression1 限制為 8000 個字元。 expression2 這是要搜尋的字元運算式。 start_location 這是搜尋開始的整數或 bigint 運算式。如果未指定 start_location ，或者它是負數或 0，搜尋就會從 expression2 開頭開始。

目前找到的原因:大多是使用like語法，所造成的。 下面紅色字體部份改成藍色字體部份就可以解決這樣的問題 strSql = "select * from Info where name like '%" + txtSearch.Text + "%' or note like '%" + txtSearch.Text + "%' order by autocount desc"; strSql = "select * from Info where InStr(1,LCase(name),LCase(\'" + txtSearch.Text + "\'),0) <> 0 or InStr(1,LCase(note),LCase(\'" + txtSearch.Text + "\'),0) <> 0 order by autocount desc";

情境說明 在實際應用中，我們常面臨需要找出某個人的最高學歷、最後工作經驗等問題(如下圖)，這些涉及到一個人對應多筆有先後順序的明細資料，你可能會常看到先撈出所有員工資料，然後在程式端進行迴圈的篩選，但若資料量過大，這種方式可能面臨效能問題，因此更好的解決方法是在資料過篩選完必要的結果後，再將其拋給程式處理。 這篇文章我要記錄一下兩個常用的寫法，假設我們有三張資料表 #TempEmployee(員工資料)、#TempEduType(學位類型)、#TempEduSchool(員工學歷)，建立測試資料表可以參考本文最下方的語法。 我們常見的子查詢的寫法，但缺點是一次只能顯示一個欄位，若要同時顯示最高學歷和其學校名稱，則必須寫兩筆子查詢。 Select emp.*, ( Select Top 1 b.[Type] From #TempEduSchool a Inner Join #TempEduType b on b.[Code]=a.[Type] Where emp.EmpNo=a.EmpNo Order by b.[Level] Desc ) as [MaxType], ( Select Top 1 a.[SchoolName] From #TempEduSchool a Inner Join #TempEduType b on b.[Code]=a.[Type] Where emp.EmpNo=a.EmpNo Order by b.[Level] Desc ) as [SchoolName] From #TempEmployee emp 使用 Outer Apply(效果類似 Left Join) or Cross Apply(效果類似 Inner Join) 的方式，詳細可參考 SQL SERVER 2005 中 Apply的用法 。 Select * From #TempEmployee emp Outer Apply ( Select Top 1 b.[Type] as [MaxType], a.[SchoolName] From #TempEduSchool a Inner Join #TempEduType b on b.[Code]=a.[Type] Where em...

情境說明 假設有一個員工的資料表 #Employee，其中有一個欄位 EmpName 記錄著員工的姓名，我們希望能夠查出在這張表內有哪些人的姓名是相同的，如下圖數據，我們期望可以找出 2 筆 Lawrence，有底下幾種常見的寫法。 若只是單純想找出，不重複的 EmpName，直接 Distinct 即可。 Select Distinct EmpName From #Employee 使用 Group By 來找出 EmpName 超過一筆以上的資料，並顯示實際的資料筆數。 Select EmpName, Count(EmpName) as RecordCount From #Employee Group By EmpName Having count(EmpName) > 1 查出單位內所有重複姓名的人完整資料表數據 Select * From #Employee Where EmpName in (Select EmpName From #Employee Group By EmpName Having count(EmpName) > 1 ) -- or Select * From #Employee main Where (Select Count(EmpName) From #Employee sub where main.EmpName = sub.EmpName ) >1 建立本文範例資料表 相關語法如下參考，使用完畢記得刪除不要的測試資料表喔。 -- 建立測試資料表 CREATE TABLE #Employee ( EmpNo varchar(10), EmpName nvarchar(20)) -- 建立測試資料 Insert Into #Employee values('001', N'Lawrence') Insert Into #Employee values('002', N'Olivier') Insert Into #Employee values('003', N'Lawrence') Insert Into #Employee values('004', N'Lawrence...

查詢結果格式化成特定字數，例如 查詢出 39 格式化成039 select right(cast(power(10,3) as varchar) + caseno ,3) as caseno from ofdoc 分析 1、select power(10,3)得到1000 2、select cast(1000+33 as varchar） 將1000轉換類型 3、select right(100039,3) 從右邊取3個字串得到039

Access對於SQL的語法較為嚴謹， Num1 && Amt9 兩個欄位型態為數值，請注意以下差別 --使用Access Num1 & Amt9 兩個欄位型態為數值 select [name],dept,a.* from GDEPTF d right join ( select left(dept,2) as SQ1, sum(num1) as N1 from GPRSNF where yy='097' and (num1 0 or amt9 0) group by left(dept,2) ) a on a.SQ1=left(d.dept,2) where right(d.dept,3)='000' --使用SQL Server 型態方式寫成字串一樣可以執行 select [name],dept,a.* from GDEPTF d right join ( select left(dept,2) as SQ1, sum(num1) as N1 from GPRSNF where yy='097' and (num1 '0' or amt9 '0') group by left(dept,2) ) a on SQ1=left(d.dept,2) and right(dept,3)='000'

過濾重複值得SQL語法為 Distinct ，但必須為全部欄位都相符才會過濾出，要是遇到如下圖有一欄位值不同則會全部的資料都過顯示出來，因此必須採用別種方法將資料過濾出來，主要我要將Class欄位內重複值且Show值為 Y 的值過濾出來