將Excel匯入sql

一般的方式是從SQL界面操作,選擇匯入Excel...

來練練，其中的重點備忘

1.整理Excel資料
  ●不允許Null的欄位，請注意有沒有預設值，否則就手動填入。
  ●存成csv檔。

2.操作SSIS。 (資料庫 右鍵> 工作Task > 匯入資料)
  ●第1個欄位是自動編號，請按下「編輯對應」→ 勾選「啟用識別插入」(英文：Enable identity insert)
  ●其他欄位型態如int, nvarchar，"來源類型"要選擇相對應型態，錯誤訊息會有提示。



(點圖放大)

參考：
使用 SQL Server 2005 的 SSIS 匯入 Excel 的既有資料
https://blog.xuite.net/j2ee/code/13981166-%E4%BD%BF%E7%94%A8+SQL+Server+2005+%E7%9A%84+SSIS+%E5%8C%AF%E5%85%A5+Excel+%E7%9A%84%E6%97%A2%E6%9C%89%E8%B3%87%E6%96%99

資料行對應 (SQL Server 匯入和匯出精靈)
https://docs.microsoft.com/zh-tw/sql/integration-services/import-export-data/column-mappings-sql-server-import-and-export-wizard?view=sql-server-2017

撰寫觸發程序時，無法在 'inserted' 和 'deleted' 資料表上使用 text、ntext 或 image 資料行。

撰寫觸發程序時遇到錯誤訊息：
無法在 'inserted' 和 'deleted' 資料表上使用 text、ntext 或 image 資料行。


原因是觸發資料表有欄位型態： text、ntext 或 image 。
微軟公司建議 請改用 varchar(max)、 nvarchar(max)和 varbinary(max) 。

完畢。

交易處理(Transaction)介紹

說到交易處理，最常舉的例子就是銀行 金錢 提款



Round 1 【交易四大特性】 **********************************************

交易處理(Transaction)

【交易四大特性】
一、單元性(Atomicity)
二、一致性(Consistency)
三、隔離性(Isolation)
四、持久性(Durability)

1 單元性(Atomicity)
【定義】
將交易過程的所有對資料庫操作視為同一個單元工作，其中可能包括許
多步驟，這些步驟要嘛全部執行成功，否則，整個交易宣告失敗。所以，
整個交易是一個不可分割的邏輯單位。
但是，在單元工作中，如果其中有一個操作尚未完成，則整個交易必須
回到初始狀態，回到初始狀態的程序稱為復原(Recovery, Rollback) 。


2 一致性(Consistency)
【定義】
指交易過程所異動的資料在交易前與交易後必須一致，資料庫的資料必
須仍然滿足完整性限制條件(利用資料表中的Check與Foreign Key)，即
維持資料的一致性


2.3 隔離性(Isolation)
【定義】隔離性是指多筆交易在同時交易時，雖然各交易是並行執行，
不過各交易之間應該滿足獨立性，也就是說，一個交易不會影響到其它
交易的執行結果，或被其它交易所干擾。

A交易處理某帳號時，B交易正好要讀取此帳號，當A交易處理失敗，
A交易Abort(撤回)，形成B交易所讀取的資料是不正確的，也必須要被Abort(撤回)。

【分析】A交易資料更新到一半尚未完成確認(Commit)時，卻被B交易讀取，
因此，B交易只是取得A交易的暫時性資料，此現象就稱為Dirty Read。

【解決方法】利用鎖定(Lock)資料的方式來隔離交易。


4 持久性(Durability)
【定義】
永久性是指當交易完成執行確認交易（Commit）後，資料庫會保存交
易後的結果，即使系統掛了，交易的結果也不能遺失。




【兩種機制與ACID分析】
資料庫系統的交易管理是指「並行控制」和「回復技術」兩個機制的合
稱，因此，我們可以將兩種機制與ACID分析如下：
(1)「並行控制」機制是要維持「隔離性」和「一致性」保持
(2)「回復技術」機制是維持交易處理的「不可分割性」和「永久性」


Round 2 交易的狀態 **********************************************


交易的狀態
一個交易狀態是由活動狀態(Active)、部分確認(Partially Committed)、確
認(Committed)、失敗(Failed)及終止狀態(Terminated)等五個狀態組合而成。

1. 活動狀態(Active State)
【定義】
當「交易開始(Begin Transaction)」執行時即進入「活動狀態(Active
State)」，在此狀態中可以對資料庫進行一系列的讀(Read)及寫(Write)
動作。

2. 部分確認狀態
(Partially Committed State)
【定義】
指在對資料庫進行各種單元操作完成之後，也就是交易結束。此時即可
進入「部分確認狀態(Partially Committed State)」，在此狀態中「同
步控制」動作將會去檢查是否干擾其他正在執行中的交易。

3. 確認狀態(Committed State)
【定義】
當「活動狀態」與「部分確認狀態」檢查動作都成功之後，即可進入
「確認狀態(Committed State)」，亦即將交易過程真正的寫入資料庫
中，表示此筆交易成功。

4. 失敗狀態(Failed State)
【定義】
當「活動狀態」或「部分確認狀態」檢查動作其中一項失敗時，此時會
被要求進入「失敗狀態」，在此狀態中交易將會寫入「UNDO取消」動
作，以回復到交易未執行前的狀態。

5. 終止狀態(Terminated State)
【定義】
是指在「交易失敗」或「交易成功」之後，最後都必須執行交易終止，
亦即結束交易(End Transaction)。


若要結束交易功能的話，有兩種情況：

下達確認(Commit)或撤回(Rollback)指令這兩種情況才會使交易結束。因此，如果在交
易處理當中，若執行的操作有成功的話，可以使用確認(Committed)指令。執行確認指令
之後，交易功能的處理結果就會真正被反映出來。

如果在交易處理當中，若執行的操作失敗時，或想要重新再來一次的話，可以執行撤回
(Rollback)指令。執行撤回指令之後，原來的交易操作會變成無效，資料會回到原本執行
處理之前的狀態。



Round 3 交易的進行 **********************************************


交易的進行
【定義】
一個完整且成功的交易，必須要經過一連串的交易動作，因此，我們必
須要了解每一個交易動作的目的。如下所示：

1.BEGIN TRANSACTION(又可寫成BEGIN TRAN)

2.READ或WRITE

3.同步控制動作檢查

--對資料庫的各種操作完成之後，即可進入部分確認狀態，並且準備進入Commit
--同步控制動作將檢查其是否干擾其他正在執行中的交易


4.COMMIT TRANSACTION
(又可寫成COMMIT TRAN、COMMIT或COMMIT WORK)

--【定義】對資料庫的各種操作完成之後，即可進入部分確認狀態，並且準備進入Commit

--如果交易執行過程沒有錯誤，下達COMMIT指令，將交易更改的資料實際寫入資料庫

5.ROLLBACK TRANSACTION
(又可寫成ROLLBACK TRAN, ROLLBACK 或ROLLBACK WORK)

--【定義】回復交易（Rollback）：如果交易執行過程有錯誤，就是下達ROLLBACK指令放棄交易，並將資料庫回復到交易前狀態
--如果交易失敗，回復交易是使用ROLLBACK TRAN指令結束。

6.UNDO
--【定義】與ROLL BACK動作相似，但是只會被用來回復到未進行單一動作前的
狀態，而不是整個交易。

7.REDO
--【定義】這是要重複執行某一交易中的動作，以確定所有已被確認的交易動作已
經成功的作用在資料庫中。




Ex 1 ================================

BEGIN TRAN
INSERT 學生資料表VALUES('S001', '張三')

IF @@ERROR<>0 --同步控制動作檢查
  ROLLBACK TRAN --確認交易（Commit）
ELSE
  COMMIT TRAN        --回復交易（Rollback）
END TRAN


Ex 2 ================================

Begin Transaction --開始交易
SQL命令1
SQL命令2
……
SQL命令N

if (產生錯誤) --進行ROLLBACK的動作
Rollback transaction
else
Commit transaction --交易成功
End Transaction --結束交易

================================


**********************************************

此篇為筆記，資料來源 洪瑞展老師。

mssql資料型態介紹: nvarchar, ntext

n        unicode
var     非固定長度，4000字以內的文字
ntext  unicode，4000字以上的文字


varchar
長度為 n 個字元之可變長度非 Unicode 字元資料。n 必須介於 1 至 8,000，儲存大小為實際輸入資料的長度，而非 n 個位元組。輸入的資料長度可以是 0 字元。

ntext
可變長度的 Unicode 資料，最大長度為 2^30 - 1 (1,073,741,823) 個字元。


適用時機：
varchar：
當你的字元長度少於8000個字元，且字元內容無須以 unicode 格式儲存時，可選擇varchar　註：一個中文字及一個英文字母皆算一個字元

ntext:
在 SQL Server 中，Unicode 資料是使用 nchar、nvarchar 和 ntext 資料型別來儲存。設成這些資料型別的資料行可儲存來自多個字元集的字元。若資料行項目所包含的 Unicode 字元個數並不一致 (最多為 4,000) ，請使用 nvarchar。若資料行每個項目的長度是固定的 (最多為 4,000 個 Unicode 字元)，請使用 nchar。若資料行的任何項目長度超過 4,000 個 Unicode 字元，則使用 ntext。



這篇教學文章寫得很好(出處 http://www.dotblogs.com.tw/jeff-yeh/archive/2010/11/14/19440.aspx)

認識Char/NChar/VarChar/NVarChar/Text/NText

Char/NChar/VarChar/NVarChar/Text/NText,這幾個資料型別或許有些人很熟,有些人只熟其中幾個,尤其是程式設計師,不碰DB Design的,如果有碰到,也可能依"慣例"的去設定資料型別,所以不難看見,有些Table資料型別很單調,看的到的型別不多,只要是存文字的,都是NVarChar,或許在瞭解後,可以做出更好的規劃. 其實這個的識別方式很簡單. 帶Var的 : 代表就是非固定長度的型別,所以Var在存不固定長度的資料時,會較省空間, 帶N的 : 就是支援Unicode,但要注意,儲存的資料會大兩倍. 這樣就把Char全部講完,剩Text跟Char有什麼不同,Text的特性跟VarChar一樣,只是用來儲存大量的文字資料,但後來的VarChar也做了調整,也可以存大量的文字資料,所以Text在未來新版的DB會移除. Char的長度為1~8000 VarChar的長度為1~8000,如果是VarChar(max)就是2^31-1 位元組,VarChar(max)只有SQL2005(含)以上才支援,SQL2000就沒有. Text的長度為2^31-1,它的長度跟VarChar(max)是一樣的. 如果您有支援多國語言的網站，請考慮利用 Unicode nchar 或 nvarchar 資料類型，將字元轉換問題減到最少。如果您使用 char 或 varchar，我們建議您執行下列動作： 當資料行資料項目的大小一致時，請使用 char。 當資料行資料項目的大小變化相當大時，請使用 varchar。 當資料行資料項目的大小變化相當大，且大小可能超出 8,000 位元組時，請使用 varchar(max)。

trigger 初體驗

trigger真是不錯的好東西




善心人士寫的入門指導文章 : http://deanma.blogspot.tw/2012/02/ms-sql-triggertable.html

的指導範例 :

--建立測試表格 CREATE TABLE [dbo].[WIPPrd](     [ProdNo] [varchar](16) NOT NULL,     [PnameD] [nvarchar](100) NULL,     [PType] [varchar](4) NULL,     [InDay] [datetime] NULL,     [UsrNo] [varchar](10) NULL,     [UsrCo] [varchar](5) NULL) ON [PRIMARY]   --建立記錄檔 CREATE TABLE [dbo].[WIPPrd_Log](     [MState] [varchar] (10) NOT NULL,     [ProdNo] [varchar](16) NOT NULL,     [PnameD] [nvarchar](100) NULL,     [PType] [varchar](4) NULL,     [InDay] [datetime] NULL,     [UsrNo] [varchar](10) NULL,     [UsrCo] [varchar](5) NULL )     --建立TRIGGER-在WIPPrd表格更新、新增、刪除後觸發 CREATE TRIGGER dbo.TR_WIPPrd_Modify on dbo.WIPPrd AFTER UPDATE,INSERT,DELETE AS BEGIN       --表格異動資料時會產生暫存的inserted和deleted兩個表格     --兩個表格格式資訊皆與原表格相同     --inserted紀錄insert資料、update後資料     --deleted紀錄delete資料、update前資料          --依據異動方式將異動資料新增到記錄檔     --inserted和deleted皆有資料表示為-UPDATE     IF EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)     BEGIN               insert into WIPPrd_Log select 'DELETE',* from deleted         insert into WIPPrd_Log select 'INSERT',* from inserted     END     --inserted有資料deleted無資料表示為-INSERT     ELSE IF EXISTS (select 1 from inserted) and Not EXISTS (select 1 from deleted)         insert into WIPPrd_Log select 'INSERT',* from inserted     --inserted無資料deleted有資料表示為-DELETE     ELSE IF NOT EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)         insert into WIPPrd_Log select 'DELETE',* from deleted  END --測試 --清空資料  delete from WIPPrd  delete from WIPPrd_log --新增資料 insert into WIPPrd values ('11','TEST_11','11',GETDATE(),'Dean','10001') insert into WIPPrd values ('22','TEST_22','22',GETDATE(),'Dean','10001') --更新資料 update WIPPrd set ProdNo='33',PnameD='TEST_33' where ProdNo='22' --刪除資料 delete from WIPPrd --查詢記錄檔 select * from WIPPrd_log ------------------------------------------------------------ INSERT     11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001 INSERT     22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001 DELETE    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001 INSERT     33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001 DELETE    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001 DELETE    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001

字串補零 小技巧

例如抓月份和日期的時候，就不需要用if 來判斷比10小，前頭補零，

直接用right 來補零，簡單明瞭。

r_from_year+1911& right("00"&r_from_month,2) &right("00"&r_from_day,2)

資料庫升級後，asp無法連到資料庫

舊系統 os : win2003  db : sql server 2000
新系統 os : win2008  db : sql server 2008 R2 Express

只是把ap環境升級，就出現下列訊息

Microsoft OLE DB Provider for ODBC Drivers 錯誤 '80004005' [Microsoft][ODBC SQL Server Driver][DBNETLIB]無效的連線。 /admin/index111.asp, 行21

(看不到錯誤訊息，或訊息顯示“網站無法顯示該網頁”，請參考網頁執行時"網站無法顯示該網頁")



新系統 db 升級改裝 : sql server 2008 R2 正式版，沒有錯誤訊息。




是資料庫升級的關係。
在連線字串IP後面，加上埠號。

Set conn = Server.CreateObject("ADODB.Connection")    ConnectString ="Driver={sql server};server=999.999.999.999,1433;uid=cat;pwd=cat;Database=CAT"      conn.Open ConnectString

完畢。

[sql]抓取最新資料

每五分鐘抓取資料。資料庫有一欄位CreateTime，格式是字串yyyyMMddhhmmss，作為版本識別。


Select COUNT(*)   FROM [DBNAME].[dbo].[Tpe]
Where CreateTime = (select Max(CreateTime) FROM [DBNAME].[dbo].[Tpe] ) 

壓縮log檔語法.sql

use DBName
backup log DBName with no_log
dbcc shrinkfile(DBName_Log,1)


當log 膨脹太大，下指令瘦身一下

[sql]找出重複的資料

檢查有沒有重複的資料


SELECT ID,COUNT(1)          /*重複的次數*/ 
FROM [View_1] 
GROUP BY ID 
HAVING COUNT(*) > 1      /*重複出現超過一次的資料*/



完畢。

[sql] update 指令用replace

update WebEditorDetail
set detail_desc =replace(detail_desc, '舊的', '新的')
WHERE detail_id='2037'


如果欄位型態是ntxt, 需要轉型，如下
 update WebEditorDetail
set detail_desc =replace(cast(detail_desc  as varchar(8000)),'舊的', '新的')
WHERE detail_id='2037'

完畢。