메모장입니다.

1. SCOPE_IDENTITY() 사용

INSERT INTO [TABLE] ([COLUMN]) VALUE ('value')
SELECT SCOPE_IDENTITY() AS pk

2. OUTPUT 사용

INSERT INTO [TABLE] ([COLUMN])
OUTPUT INSERTED.[PK_COLUMN]
SELECT 'value' FROM [TABLE2]   -- 그냥 insert 도 되고 insert select 도 사용 가능

3. INSERT 된 PK와 기존PK 얻는 방법(MERGE INTO 사용)

MERGE INTO [TARGET_TABLE] TT
USING (
    SELECT [PK_NAME], [COLUMN] FROM [SOURCE_TABLE] WHERE [COLUMN] = ?
) ST ON TT.[PK_COLUMN] = ST.[PK_COLUMN]
WHEN NOT MATCHED THEN
    INSERT ([TT_COLUMN]) VALUES (ST.[COLUMN])
OUTPUT INSERTED.[TT_PK_NAME] AS NewPK, ST.[PK_NAME] AS OldPK;

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy HH\:mm\:ss');
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy HH\:mm\:ss');
SELECT FORMAT(CAST('2018-09-12 11:22:33' AS DATETIME2), 'MM/dd/yyyy HH\:mm\:ss');

WITH T
     AS (SELECT TOP 3 *
         FROM   Table1
         ORDER  BY id DESC)
DELETE FROM T

DELETE TOP(1) FROM Table1

UPDATE TableA (또는 Alias이름)
SET foo = B.bar
FROM TableA A
    INNER JOIN TableB B ON A.col1 = B.colx
WHERE ...

DELETE TableA (또는 Alias이름)
FROM TableA A
    INNER JOIN TableB B ON A.ID=B.ID
WHERE ....

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);

1. 전체 테이블명

SELECT * FROM information_schema.TABLE_CONSTRAINTS;

2. 전체테이블의 컬럼명

SELECT * FROM information_schema.Columns

3. 테이블별 PK 정보

SELECT * FROM information_schema.KEY_COLUMN_USAGE

4. LAST_INSERT_ID

SELECT SCOPE_IDENTITY() AS last_insert_id;

5. index 목록

SELECT
        TableName = t.name,
        IndexName = ind.name,
        IndexId = ind.index_id,
        ColumnId = ic.index_column_id,
        ColumnName = col.name,
    ind.*,
    ic.*,
    col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
ORDER BY
    ind.name, t.name, ind.index_id, ic.index_column_id

6. ???

SELECT
    SC.DisplayName,
    SR.SearchCategory,
    CoverageType = STUFF((
         SELECT ',' + SR1.CoverageType
         FROM tblSearchReq SR1 WHERE SR.SearchCategory=SR1.SearchCategory AND SR1.CoverageType <> 'Nationwide'
         GROUP BY SR1.SearchCategory, SR1.CoverageType
         FOR XML PATH(''), TYPE).value('.', 'VARCHAR(100)'), 1, 1, ''
    )
FROM
    SearchCategory SC
    INNER JOIN tblSearchReq SR ON SC.CategoryName=SR.SearchCategory
WHERE
    SR.CoverageType <> 'Nationwide'
GROUP BY
    SC.DisplayName, SR.SearchCategory
ORDER BY
    SC.DisplayName