---
# System prepended metadata

title: SQL 筆記本
tags: [SQL]

---

---
title: 'SQL 筆記本'
disqus: hackmd
tags: SQL
---

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'
```
```
