IF OBJECT_ID (N'DBO.FNC_DML_MRG') IS NOT NULL
DROP FUNCTION DBO.FNC_DML_MRG;
GO
CREATE FUNCTION [DBO].[FNC_DML_MRG]( @P_TABLE_NAME VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
WITH DUAL
AS
(
SELECT 0 AS NO
, TBL.TABLE_SCHEMA AS TABLE_SCHEMA
, TBL.TABLE_NAME AS TABLE_NAME
, CAST(CMT.VALUE AS VARCHAR(MAX)) AS TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES TBL
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON CMT.MAJOR_ID = OBJECT_ID(TBL.TABLE_SCHEMA + '.'+ TBL.TABLE_NAME)
AND CMT.MINOR_ID = 0
WHERE TBL.TABLE_NAME = @P_TABLE_NAME
UNION ALL
SELECT NO + 1 AS NO
, TABLE_SCHEMA AS TABLE_SCHEMA
, TABLE_NAME AS TABLE_NAME
, TABLE_COMMENT AS TABLE_COMMENT
FROM DUAL
WHERE NO + 1 <= 20
)
, TBL_COL
AS
(
SELECT COL.TABLE_SCHEMA AS TABLE_SCHEMA
, COL.TABLE_NAME AS TABLE_NAME
, D.NO
, COL.ORDINAL_POSITION AS COLUMN_NO
, COL.COLUMN_NAME AS COLUMN_NAME
, CST.IS_PK
, CASE COL.IS_NULLABLE
WHEN 'YES' THEN 'Y'
WHEN 'NO' THEN 'N'
END AS IS_NULLABLE
, REPLACE(REPLACE(COL.COLUMN_DEFAULT, '(', ''), ')', '') AS COLUMN_DEFAULT
, CONCAT ( '--'
, RTRIM(CONCAT(SPACE(1), CST.PK))
, CONCAT(SPACE(1), CAST(CMT.VALUE AS VARCHAR(MAX)))
, RTRIM(CONCAT(SPACE(1), CST.AK))
, RTRIM(CONCAT(SPACE(1), CST.FK))) AS COLUMN_COMMENT
, CASE COL.COLUMN_NAME
WHEN 'SYS_ID' THEN '#{g.tenant}'
WHEN 'REG_ID' THEN '#{g.username}'
WHEN 'REG_DT' THEN '#{g.now}'
WHEN 'MOD_ID' THEN '#{g.username}'
WHEN 'MOD_DT' THEN '#{g.now}'
ELSE CONCAT('#{p.', LOWER(COL.COLUMN_NAME), '}')
END AS COLUMN_PARAM
, CASE COL.ORDINAL_POSITION
WHEN FIRST_VALUE(COL.ORDINAL_POSITION) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.ORDINAL_POSITION)
THEN 'Y'
ELSE 'N'
END AS IS_FST
, CASE COL.ORDINAL_POSITION
WHEN LAST_VALUE(COL.ORDINAL_POSITION) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.ORDINAL_POSITION
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
THEN 'Y'
ELSE 'N'
END AS IS_LST
FROM INFORMATION_SCHEMA.COLUMNS COL
JOIN SYS.ALL_COLUMNS CLU ON CLU.OBJECT_ID = OBJECT_ID(COL.TABLE_SCHEMA + '.' + COL.TABLE_NAME)
AND CLU.NAME = COL.COLUMN_NAME
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON CMT.MAJOR_ID = CLU.OBJECT_ID
AND CMT.MINOR_ID = CLU.COLUMN_ID
AND CMT.CLASS = 1
LEFT OUTER JOIN (SELECT T.TABLE_ID
, T.COLUMN_ID
, MIN(CASE T.TYPE WHEN 'PK' THEN 'Y' END) AS IS_PK
, MIN(CASE T.TYPE WHEN 'PK' THEN 'PK(' + T.POSITION + ')' END) AS PK
, MIN(CASE T.TYPE WHEN 'UQ' THEN 'Y' END) AS IS_AK
, STUFF( (SELECT ', ' + CST.NAME + '(' + CAST(IDC.INDEX_COLUMN_ID AS VARCHAR(MAX)) + ')'
FROM SYS.KEY_CONSTRAINTS CST
JOIN SYS.INDEX_COLUMNS IDC ON IDC.OBJECT_ID = CST.PARENT_OBJECT_ID
AND IDC.INDEX_ID = CST.UNIQUE_INDEX_ID
WHERE CST.PARENT_OBJECT_ID = T.TABLE_ID
AND IDC.COLUMN_ID = T.COLUMN_ID
AND CST.TYPE = 'UQ'
FOR XML PATH('')), 1, 2, '') AS AK
, MIN(CASE T.TYPE WHEN 'F' THEN 'Y' END) AS IS_FK
, STUFF( (SELECT ', ' + FKY.NAME + '(' + CAST(FKC.CONSTRAINT_COLUMN_ID AS VARCHAR(MAX)) + ')'
FROM SYS.FOREIGN_KEYS FKY
JOIN SYS.FOREIGN_KEY_COLUMNS FKC ON FKC.CONSTRAINT_OBJECT_ID = FKY.OBJECT_ID
WHERE FKY.PARENT_OBJECT_ID = T.TABLE_ID
AND FKC.PARENT_COLUMN_ID = T.COLUMN_ID
FOR XML PATH('')), 1, 2, '') AS FK
FROM (SELECT CST.NAME
, CST.TYPE
, CST.PARENT_OBJECT_ID AS TABLE_ID
, IDC.COLUMN_ID AS COLUMN_ID
, CAST(IDC.INDEX_COLUMN_ID AS VARCHAR(MAX)) AS POSITION
FROM SYS.KEY_CONSTRAINTS CST
JOIN SYS.INDEX_COLUMNS IDC ON IDC.OBJECT_ID = CST.PARENT_OBJECT_ID
AND IDC.INDEX_ID = CST.UNIQUE_INDEX_ID
UNION ALL
SELECT FKY.NAME
, FKY.TYPE
, FKY.PARENT_OBJECT_ID AS TABLE_ID
, FKC.PARENT_COLUMN_ID AS COLUMN_ID
, CAST(FKC.CONSTRAINT_COLUMN_ID AS VARCHAR(MAX)) AS POSITION
FROM SYS.FOREIGN_KEYS FKY
JOIN SYS.FOREIGN_KEY_COLUMNS FKC ON FKC.CONSTRAINT_OBJECT_ID = FKY.OBJECT_ID
) T
GROUP BY T.TABLE_ID
, T.COLUMN_ID
) CST ON CST.TABLE_ID = CLU.OBJECT_ID
AND CST.COLUMN_ID = CLU.COLUMN_ID
JOIN (SELECT NO
, TABLE_SCHEMA
, TABLE_NAME
FROM DUAL) D ON D.NO IN (2, CASE WHEN CST.IS_PK = 'Y' THEN 5 END, CASE WHEN CST.IS_PK IS NULL THEN 11 END, 14, 15)
AND D.TABLE_SCHEMA = COL.TABLE_SCHEMA
AND D.TABLE_NAME = COL.TABLE_NAME
)
, TBL_DML
AS
(
SELECT C.TABLE_SCHEMA AS TABLE_SCHEMA
, C.TABLE_NAME AS TABLE_NAME
, C.NO AS NO
, C.COLUMN_NO AS COLUMN_NO
, SPACE(8) AS COL1
, CASE
WHEN C.NO = 2 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('USING' , SPACE(8)), 8)
ELSE SPACE(8)
END AS COL2
, CASE
WHEN C.NO = 2 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('(SELECT' , SPACE(8)) , 8)
WHEN C.NO = 2 THEN LEFT(CONCAT(',' , SPACE(8)) , 8)
WHEN C.NO = 5 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('ON (' , SPACE(12)), 12)
WHEN C.NO = 5 THEN LEFT(CONCAT(' AND' , SPACE(12)), 12)
WHEN C.NO = 11 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('UPDATE SET' , SPACE(12)), 12)
WHEN C.NO = 14 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('INSERT (' , SPACE(12)), 12)
WHEN C.NO = 15 AND C.IS_FST = 'Y' THEN LEFT(CONCAT('VALUES (' , SPACE(12)), 12)
WHEN C.NO IN (11, 14, 15) THEN LEFT(CONCAT(SPACE(8), ',' , SPACE(12)), 12)
ELSE SPACE(8)
END AS COL3
, CASE
WHEN C.NO = 2 AND C.IS_NULLABLE = 'Y' THEN LEFT(CONCAT(C.COLUMN_PARAM , SPACE(44)), 44)
WHEN C.NO = 2 AND C.IS_NULLABLE = 'N' THEN LEFT(CONCAT('ISNULL(', C.COLUMN_PARAM, ', ', C.COLUMN_DEFAULT , ')' , SPACE(44)), 44)
WHEN C.NO = 5 THEN LEFT(CONCAT('S.', C.COLUMN_NAME , SPACE(40)), 40)
WHEN C.NO = 11 THEN LEFT(CONCAT(C.COLUMN_NAME , SPACE(40)), 40)
WHEN C.NO = 14 AND C.IS_LST = 'N' THEN LEFT(CONCAT(C.COLUMN_NAME , SPACE(40)), 40)
WHEN C.NO = 14 AND C.IS_LST = 'Y' THEN LEFT(CONCAT(C.COLUMN_NAME, ')' , SPACE(40)), 40)
WHEN C.NO = 15 AND C.IS_LST = 'N' THEN LEFT(CONCAT('S.', C.COLUMN_NAME , SPACE(40)), 40)
WHEN C.NO = 15 AND C.IS_LST = 'Y' THEN LEFT(CONCAT('S.', C.COLUMN_NAME, ')' , SPACE(40)), 40)
ELSE SPACE(8)
END AS COL4
, CASE
WHEN C.NO = 2 THEN LEFT(CONCAT('AS ', C.COLUMN_NAME , SPACE(40)), 40)
WHEN C.NO = 5 AND C.IS_LST = 'N' THEN CONCAT('= T.', C.COLUMN_NAME)
WHEN C.NO = 5 AND C.IS_LST = 'Y' THEN CONCAT('= T.', C.COLUMN_NAME, ')')
WHEN C.NO = 11 THEN LEFT(CONCAT('= S.', C.COLUMN_NAME , SPACE(40)), 40)
ELSE ''
END AS COL5
, CASE C.NO
WHEN 5 THEN ''
ELSE C.COLUMN_COMMENT
END AS COL6
, C.COLUMN_NAME
, C.IS_PK
, C.IS_NULLABLE
, C.COLUMN_DEFAULT
, C.COLUMN_COMMENT
, C.COLUMN_PARAM
, C.IS_FST
, C.IS_LST
FROM TBL_COL C
UNION ALL
SELECT D.TABLE_SCHEMA AS TABLE_SCHEMA
, D.TABLE_NAME AS TABLE_NAME
, D.NO AS NO
, 0 AS COLUMN_NO
, CASE D.NO
WHEN 0 THEN CONCAT('-- ', D.TABLE_COMMENT)
WHEN 1 THEN CONCAT('MERGE INTO', SPACE(1))
ELSE SPACE(8)
END AS COL1
, CASE D.NO
WHEN 0 THEN ''
WHEN 1 THEN CONCAT(D.TABLE_NAME, SPACE(1), 'T')
WHEN 7 THEN 'WHEN '
WHEN 10 THEN 'WHEN '
WHEN 13 THEN 'WHEN '
ELSE SPACE(8)
END AS COL2
, CASE D.NO
WHEN 3 THEN LEFT(CONCAT(',' , SPACE(8)) , 8)
WHEN 4 THEN LEFT(CONCAT(')' , SPACE(8)) , 8)
WHEN 7 THEN 'MATCHED AND S.DEL_YN = ' + CHAR(39) + 'Y' + CHAR(39) + ' THEN'
WHEN 8 THEN 'DELETE'
WHEN 10 THEN 'MATCHED THEN'
WHEN 13 THEN 'NOT MATCHED THEN'
ELSE ''
END AS COL3
, CASE D.NO
WHEN 3 THEN LEFT(CONCAT('#{p.del_yn}', SPACE(44)), 44)
WHEN 4 THEN 'S'
ELSE ''
END AS COL4
, CASE D.NO
WHEN 3 THEN LEFT(CONCAT('AS DEL_YN', SPACE(40)), 40)
ELSE ''
END AS COL5
, CASE D.NO
WHEN 3 THEN '-- 삭제대상여부[Y/N]'
ELSE ''
END AS COL6
, NULL AS COLUMN_NAME
, NULL AS IS_PK
, NULL AS IS_NULLABLE
, NULL AS COLUMN_DEFAULT
, D.TABLE_COMMENT AS COLUMN_COMMENT
, NULL AS COLUMN_PARAM
, NULL AS IS_FST
, NULL AS IS_LST
FROM DUAL D
WHERE D.NO NOT IN (2, 5, 11, 14, 15)
AND D.NO <= 15
)
, TBL_MRG
AS
(
SELECT T.COL1
, T.COL2
, T.COL3
, T.COL4
, T.COL5
, T.COL6
, ROW_NUMBER() OVER (ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, T.NO, T.COLUMN_NO) AS NO
, T.TABLE_SCHEMA
FROM TBL_DML T
WHERE T.TABLE_NAME = @P_TABLE_NAME
)
SELECT COL1
, COL2
, COL3
, COL4
, COL5
, COL6
, CASE
WHEN NO > 0 THEN ''
END AS COL7
--, TABLE_SCHEMA
FROM TBL_MRG
);
GO
SQL Server 버전 MERGE INTO
|
2017.11.05 04:21:08
|
2021.09.23 09:43:42
|
432
|
Aiden
Total of Attached file
0.00 Bytes of 0 files
2023.05.16
2019.11.04
2018.09.10
2017.11.05
2017.10.29
2016.09.20
2016.01.21