OpenQuery 可
---
當資料是需要彈性Server時
```sql=
DECLARE @StrStartDate VARCHAR(20),
@sqlCommand varchar(MAX)
SET @StrStartDate = CONVERT(varchar(20), @StartDt, 120)
SET @sqlCommand = 'SELECT cg.GroupID, cg.GroupName
FROM [OBDataCenter].[dbo].[CCRGroup] cg (NOLOCK)
WHERE cg.CreateDate >= '''''+ @StrStartDate +'''''
AND cg.DisableFlag = ''''0''''
AND cg.ProjectID = '''''+ @ProjectID + ''''''
SET @sqlCommand = 'SELECT * FROM OPENQUERY(['+ @Server +'],'''+ @sqlCommand +''')'
EXEC(@sqlCommand)
```
逐筆更新
---
當要更新的表單很常被Query，但同時又要更新大筆資料時，除了NOLOCK，也可以用迴圈逐筆更新，Query才不會太慢
```sql=
CREATE TABLE #tmpTable(
Column1 INT,
Column2 VARCHAR(40),
RowsNum INT IDENTITY(1,1)
)
CREATE INDEX IDX_tmpTable_RowsNum ON #tmpTable(Column1, Column2, RowsNum)
-- 迴圈宣告
DECLARE @rc INT;
DECLARE @tableRows INT;
DECLARE @batchSize INT;
DECLARE @start INT;
DECLARE @end INT;
-- 預設每次更新筆數
SET @batchSize = 1000;
-- 資料總筆數
SELECT @tableRows = (MAX(tcc.RowsNum) + 1) FROM #tmpTable tcc;
SET @rc = 1;
SET @start = 1;
SET @end = @start + @batchSize - 1;
WHILE @rc < @tableRows
BEGIN
UPDATE cr
SET
Column4 = '123',
Column4 = '456',
FROM #tmpTable tcci
JOIN DemoTable cr (NOLOCK) ON cr.Column1 = @Column1
AND cr.Column1 = tcci.Column1
AND cr.Column2 = tcci.Column2
WHERE tcci.RowsNum BETWEEN @start AND @end
SET @rc += @batchSize;
SET @start = @end + 1;
SET @end = @start + @batchSize - 1;
END
```
新舊資料比對(檢查)
---
```sql=
-- 1.先放至temp表單中
IF OBJECT_ID('tempdb..#tmpProject') IS NOT NULL DROP TABLE #tmpProject
SELECT 'Project' AS TableName,p.ProjectGUID, p.SystemGUID, p.ProjectName,
p.ImportID, p.[Server], p.DBName, p.CreateDate, p.Creator,
p.MaintainDate, p.Maintainer, p.[Status]
INTO #tmpProject
FROM [dbo].[Project] p(NOLOCK)
WHERE 1=1
AND p.ProjectGUID = @ProjectGUID
-- 2. 撈出檢查表
SELECT
'Project' AS TableName
, CASE
WHEN T.ProjectGUID IS NULL THEN N'新增'
ELSE N'修改'
END AS 'Action'
, CASE
WHEN T.ProjectGUID IS NULL THEN ''
ELSE
SUBSTRING(
CASE WHEN ISNULL(CONVERT(VARCHAR(40), T.SystemGUID), '') <> ISNULL(CONVERT(VARCHAR(40), S.SystemGUID), '') THEN ', SystemGUID' ELSE '' END
+ CASE WHEN ISNULL(T.ProjectName, '') <> ISNULL(S.ProjectName, '') THEN ', ProjectName' ELSE '' END
+ CASE WHEN ISNULL(T.ImportID, '') <> ISNULL(S.ImportID, '') THEN ', ImportID' ELSE '' END
+ CASE WHEN ISNULL(T.[Server], '') <> ISNULL(S.[Server], '') THEN ', Server' ELSE '' END
+ CASE WHEN ISNULL(T.DBName, '') <> ISNULL(S.DBName, '') THEN ' DBName' ELSE '' END
+ CASE WHEN ISNULL(T.Creator, '') <> ISNULL(S.Creator, '') THEN ', Creator' ELSE '' END
+ CASE WHEN ISNULL(T.CreateDate, '1990-01-01') <> ISNULL(S.CreateDate, '1990-01-01') THEN ', CreateDate' ELSE '' END
+ CASE WHEN ISNULL(T.Maintainer, '') <> ISNULL(S.Maintainer, '') THEN ', Maintainer' ELSE '' END
+ CASE WHEN ISNULL(T.MaintainDate, '1990-01-01') <> ISNULL(S.MaintainDate, '1990-01-01') THEN ', MaintainDate' ELSE '' END
+ CASE WHEN ISNULL(T.[Status], 0) <> ISNULL(S.[Status], 0) THEN ', Status' ELSE '' END
, 3, 1000)
END AS 'Field'
, CAST(T.ProjectGUID AS VARCHAR(50)) [Guid]
, ISNULL(S.ProjectName, '') [Name]
FROM #tmpProject S(NOLOCK)
LEFT JOIN W08DB001.NLIS.dbo.Project T(NOLOCK) ON T.ProjectGUID = S.ProjectGUID
WHERE 1=1
AND (
T.ProjectGUID IS NULL
OR ISNULL(CONVERT(VARCHAR(40), T.SystemGUID), '') <> ISNULL(CONVERT(VARCHAR(40), S.SystemGUID), '')
OR ISNULL(T.ProjectName, '') <> ISNULL(S.ProjectName, '')
OR ISNULL(T.ImportID, '') <> ISNULL(S.ImportID, '')
OR ISNULL(T.[Server], '') <> ISNULL(S.[Server], '')
OR ISNULL(T.DBName, '') <> ISNULL(S.DBName, '')
OR ISNULL(T.Creator, '') <> ISNULL(S.Creator, '')
OR ISNULL(T.CreateDate, '1990-01-01') <> ISNULL(S.CreateDate, '1990-01-01')
OR ISNULL(T.Maintainer, '') <> ISNULL(S.Maintainer,'')
OR ISNULL(T.MaintainDate, '1990-01-01') <> ISNULL(S.MaintainDate,'1990-01-01')
OR ISNULL(T.[Status], 0) <> ISNULL(S.[Status], 0)
)
```
新舊資料比對(過版)
---
當不同Server/DB，但是有同樣的Schema，需要把資料過版過去時
```sql=
UPDATE op
SET
op.SystemGUID = np.SystemGUID,
op.ProjectName = np.ProjectName,
op.ImportID = np.ImportID,
op.[Server] = np.[Server],
op.DBName = np.DBName,
op.Maintainer = np.Maintainer,
op.MaintainDate = np.MaintainDate,
op.[Status] = np.[Status]
FROM [dbo].[Project] np(NOLOCK)
JOIN [W08NJDBRD001].[NLIS].[dbo].[Project] op (NOLOCK) ON op.ProjectGUID = np.ProjectGUID
WHERE np.ProjectGUID = @ProjectGUID
SET @Count_Update_Project = @@ROWCOUNT
INSERT INTO [W08NJDBRD001].[NLIS].[dbo].[Project]
(
ProjectGUID,
SystemGUID,
ProjectName,
ImportID,
[Server],
DBName,
[Status]
)
SELECT
@ProjectGUID AS ProjectGUID,np.SystemGUID,np.ProjectName,np.ImportID,np.[Server],np.DBName,np.[Status]
FROM [dbo].[Project] np (NOLOCK)
LEFT JOIN [W08NJDBRD001].[NLIS].[dbo].[Project] op (NOLOCK) ON op.ProjectGUID = np.ProjectGUID
WHERE np.ProjectGUID = @ProjectGUID
AND op.ProjectGUID IS null
SET @Count_Insert_Project = @@ROWCOUNT
```
組合字串拆解更新
---
```sql=
DECLARE
@ExcelColumn VARCHAR(MAX)='col1,col2,col3',
@TableColumn VARCHAR(MAX)='col11,col22,col33',
@Sort VARCHAR(MAX) ='1;2;3',
@CategoryGUID UNIQUEIDENTIFIER,
@User VARCHAR(30)
DECLARE @_i int = 1,@_Count int = 0
--分割ExcelColumn存入暫存Table，並給序號
select [No] = identity(int,1,1), *
into #ExcelColumn
from dbo.f_SplitList(';', @ExcelColumn)
--分割TableColumn存入暫存Table，並給序號
select [No] = identity(int,1,1), *
into #TableColumn
from dbo.f_SplitList(';', @TableColumn)
--分割Sort存入暫存Table，並給序號
select [No] = identity(int,1,1), *
into #Sort
from dbo.f_SplitList(';', @Sort)
--取得要修改的資料數量
set @_Count = (select COUNT(*) from #ExcelColumn)
--用迴圈逐筆修改
while(@_i <= @_Count)
begin
declare @UpdateExcel varchar(100)
,@UpdateTable varchar(100)
,@UpdateSort varchar(100)
--篩選出要更新的Excelcol
set @UpdateExcel = (select ListItem from #ExcelColumn where [No] = @_i)
--篩選出要更新的Tablecol
set @UpdateTable = (select ListItem from #TableColumn where [No] = @_i)
--篩選出要更新的Sort
set @UpdateSort = (select ListItem from #Sort where [No] = @_i)
--新增資料
INSERT INTO ExcelColumnMapping
(
CategoryGUID,
ExcelColumn,
TableColumn,
Sort,
Creator,
CreateDate,
[Status]
)
VALUES
(
@CategoryGUID,
@UpdateExcel,
@UpdateTable,
@UpdateSort,
@User,
GETDATE(),
1
)
set @_i = @_i + 1
end
DROP TABLE #ExcelColumn
DROP TABLE #TableColumn
DROP TABLE #Sort
```
組合字串Self Join更新
---
```sql=
DECLARE @param VARCHAR(MAX) = N'T-XHJ-004-19A,T-ST-007-19JS,T-XHJ-004-19A'
UPDATE tb1
SET tb1.SIZE_ = 'WTF'
FROM [Test].[dbo].[Shipping] tb1
LEFT JOIN [Test].[dbo].[Shipping] tb2 ON (tb1.MPO_DETAIL_RECORD = tb2.MPO_DETAIL_RECORD)
WHERE tb1.SHIP_NO IN (SELECT [Name] FROM dbo.splitstring(@param))
select * from [Test].[dbo].[Shipping](NOLOCK)
```
字串拆解函式
---
```sql=
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
```
MERGE
---
```sql=
```
防禦式 SQL 更新
---
```sql=
SET XACT_ABORT ON
BEGIN TRAN
--實驗用，插入一筆判別 Commit 或 Rollback
INSERT INTO Product VALUES (255,'Committed', -1);
SELECT * FROM Product WHERE Id = 1;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1 not found', 1;
UPDATE Product SET Price = 990 WHERE Id = 1 AND Price = 980;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1, Price=980 not matched', 1;
SELECT * FROM Product WHERE Id = 1;
COMMIT TRAN
```
交易
---
```sql=
```
Remove Duplicate
---
```sql=
;WITH CTE AS
(
SELECT *,
RN = ROW_NUMBER() OVER( PARTITION BY ConnectionID ORDER BY ConnectionID)
FROM [Billing].[dbo].[SIP_PBXAmount] (NOLOCK)
WHERE ServerType = 5 AND Type1Connectime BETWEEN '2020/8/1' AND '2020/9/1'
)
DELETE FROM CTE
WHERE RN > 1
```
Add LinkServer
---
```sql=
EXEC master.dbo.sp_addlinkedserver
@server = N'uranium',
@srvproduct=N'SQL Server'
```
```
{"metaMigratedAt":"2023-06-15T11:30:26.124Z","metaMigratedFrom":"YAML","title":"SQL 筆記本","breaks":true,"disqus":"hackmd","contributors":"[{\"id\":\"bf4be45f-3f16-466f-ba9b-911071c79118\",\"add\":8939,\"del\":88}]","description":"當資料是需要彈性Server時"}