###### tags: `MSSQL`
# 常用MSSQL指令
最近修改日期:20201019
<style>
.blue {
color: blue;
}
.red {
color: red;
}
</style>
### 比對兩個相同的資料表
20201019
```sql=
select * from TEST1
EXCEPT
select * from TEST2
--由左側的輸入查詢資料，傳回不同的資料列(找相異資料)
```
```sql=
select * from TEST1
INTERSECT
select * from TEST2
--由左側及右側的輸入查詢資料，傳回相同的資料列(找相同資料)
```
EXCEPT 會從左側查詢中傳回在右側查詢中找不到的任何個別值。
INTERSECT 會傳回 INTERSECT 運算元左右兩側查詢都傳回的任何個別值。
使用 EXCEPT 或 INTERSECT 的兩個查詢，其結果集的基本組合規則如下：
在所有查詢中，資料行的數目和順序都必須相同。
參考:[SQL SERVER 比對兩個資料表差異 EXCEPT ; INTERSECT](https://iamtaco.pixnet.net/blog/post/45199193)
---
### 欄位自動編號
20200930
資料表建立後，尚無資料前，使用
alter table TableName drop column AutoCounter
alter table TableName add AutoCounter int identity(1,1)
方法設定之
說明:標識列， identity(a,b)，ab均為正整數，a表示開始數，b表示增幅，就像identity(1,1)意思就是該列自動增長，由1開始每次增加是1
---
### 查詢DB中所有table的資料筆數
20200928
```sql=
SELECT S.NAME '結構描述', O.NAME '資料表名稱', P.ROWS '列總數'
FROM SYS.OBJECTS O INNER JOIN SYS.SCHEMAS S
ON O.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.PARTITIONS P
ON O.OBJECT_ID = P.OBJECT_ID
WHERE (O.TYPE = 'U') AND
(P.INDEX_ID IN (0,1))
ORDER BY S.NAME, O.NAME ASC;
```
參考:[阿輝的零碎筆記](https://dotblogs.com.tw/grayyin/2017/04/28/231156)
---
### 排名函數 ROW_NUMBER、RANK、DENSE_RAN、NTILE 語法
是Sql Server2005新增的功能。
20200707
**ROW_NUMBER用法**: 依照指定的欄位排序，並逐筆加上順號的方式。
(Oracle也可以用，但是效能似乎不好:需再確認)
```sql
SELECT
ROW_NUMBER() OVER(ORDER BY AccountID) AS Account_OrderID
,*
FROM MEMBERS
```
**RANK用法:** 依照排序的欄位，相同的資料相同排名，下一個不同會【跳脫】
```sql
SELECT
RANK() OVER(ORDER BY CustomerID) AS ROWID
,*
FROM Orders
```
**DENSE_RANK用法:** 依照排序的欄位，相同的資料相同排名，下一個不同會【不跳脫】
```sql
SELECT
DENSE_RANK() OVER(ORDER BY CustomerID) AS ROWID
,*
FROM Orders
```
**NTILE用法**:可以對序號進行分組處理，將有序分區中的行分發到指定數目的組中
```sql
select NTILE( 4 ) OVER ( order by [ SubTime ] desc ) as ntile, * from [ Order ]
```
參考:
1.[[SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)](https://dotblogs.com.tw/topcat/2009/08/06/9906)
2.[Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介](https://www.cnblogs.com/52xf/p/4209211.html)
3.[關於OVER子句的使用方式](https://ithelp.ithome.com.tw/articles/10190257) 有Partition By語法。
---
### INSERT 語法:MSSQL Insert 語法
20200703 M20200710
語法:
```sq
INSERT INTO table_name
VALUES (value1_1, value2_2, value3_3,···),
(value2_1, value2_2, value2_3,···),
(value3_1, value3_2, value3_3,···),
······;
INSERT INTO table_name (column1, column2, column3,...)
SELECT othercolumn1, othercolumn2, othercolumn3,...
FROM othertable_name;
INSERT INTO table_name (欄位要列出來，用* 不行)
SELECT *
FROM othertable_name;
```
由舊資料表建立一模一樣新資料表方法
```sql
SELECT * INTO DB1.DBO.MyTABLE FROM DB2.DBO.MyTABLE WHERE 1=2 --若去除 WHERE 1=2，則連資料會一起進去。
```
與Oracle 語法:INSERT INTO TABLENAME VALUE (SELECT* FROM TABLENAME@DB2 WHERE 1=1) 有差異。
---
### 跨伺服器建立Table(View)方法
20200629 20201019
<font class='blue'>USE</font> Local_DB
<font class='blue'>EXEC</font> sp_addlinkedserver @server = '192.168.100.200', @srvproduct = N'SQL Server'
<font class='blue'>EXEC</font> sp_addlinkedsrvlogin @rmtsrvname = '192.168.100.200' , @useself = false, @rmtuser = 'remote_db_account', @rmtpassword = 'remote_db_password'
利用 sp_addlinkedserver 跟遠端資料庫SQL建立關連
利用 sp_addlinkedsrvlogin 設定登入遠端資料庫的帳號、密碼等訊息
Local_DB 換成你程式中原本使用的資料庫名稱
192.168.100.200 當然要換成你遠端伺服器主機的IP
rmtuser 換成遠端資料庫可登入SQL的帳號
rmtpasswword 換成遠端資料庫可登入SQL帳號的 密碼
```sql
CREATE VIEW Remote_view1
AS
SELECT *
FROM [192.168.100.200].Remote_DB.dbo.Remote_Table
```
嚴格的來說 SELECT statement格式為
```sql
SELECT * FROM [IP 或 Server_name].Remote_DB_name.DB_Owner_name.Remote_Table_Name
```
刪除用EXEC sp_addlinkedsrvlogin ...方法建立的連線(因為密碼書錯之故)。方法如下:
```sql
EXEC sp_droplinkedsrvlogin @rmtsrvname = '192.168.100.200',@locallogin = 'sa'
```
Ref:[SQL 建立遠端資料庫資料表到本地 Table 或 VIEW](https://blog.xuite.net/tolarku/blog/21137675-SQL+%E5%BB%BA%E7%AB%8B%E9%81%A0%E7%AB%AF%E8%B3%87%E6%96%99%E5%BA%AB%E8%B3%87%E6%96%99%E8%A1%A8%E5%88%B0%E6%9C%AC%E5%9C%B0+Table+%E6%88%96+VIEW)
參考:[sp_addlinkedsrvlogin 、 sp_droplinkedsrvlogin 使用方法](https://tsuozoe.pixnet.net/blog/post/22419129)
---
### 跨伺服器查詢
20200623
也可以操作insert 語法
```SQL
SELECT T.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};
SERVER=192.168.0.100;
UID=sa;
PWD=ncunicom_alarm',
BSC_ALARM.dbo.ALARM_LIST) AS T
/* 其中，192.168.0.100為遠端伺服器IP，也可以使用別名，
BSC_ALARM是遠端資料庫，
ALARM_LIST遠端資料庫中的資料表
*/
```
若出現==連結伺服器 "(null)" 的 OLE DB 提供者 "MSDASQL" 並未包含資料表 ""BSC_ALARM.dbo.ALARM_LIS""。該資料表不存在，或是目前的使用者沒有使用該資料表的權限。== 的錯誤。
<font class='blue'>則要開啟 開放 'd Hoc Distributed Queries 的使用權限，開啟資料庫 - Facet</font>
語法如下:
```SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'ad hoc distributed queries', 1;
GO
RECONFIGURE;
GO
```
Ref:[MS SQLSERVER如何實現跨伺服器查詢](https://www.itread01.com/content/1547635336.html)
Ref:[SQL使用OPENROWSET出現訊息 SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取](https://dotblogs.com.tw/atalin/2010/11/18/19531)
---
### CREATE TABLE 建立資料表方法
20200622
由另外的MS資料庫，建立一模一樣的資料表到新的MS資料庫。
WHERE 1=2 不複製資料過去。(若要連資料一起過去，可用1=1或是移除 WHERE 1=2)
```SQL
SELECT * INTO NEWDB.DBO.DEPT FROM OLDDB.DBO.DEPT WHERE 1=2
```
註:與Oracle 用CREATE TABLE ..的手法不同，特別紀錄一下。
---
S20200622 By YTC
M20200623 ,M20200629 ,M20200703
M20200707 調正順序，新的在上面。並加入MSSQL 較特別的ROW_NUMBER、RANK 等語法。
M20200710
M20200928 加入查詢DB中所有table的資料筆數語法
M20200929
M20201019 加入資料表比對查詢,加入[跨伺服器建立Table(View)方法] 之刪除錯誤資料
Sign in with Wallet
Connect another wallet