IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertSeverityProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertSeverityProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertSeverityProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
DECLARE @utc DATETIME = GETUTCDATE()
DECLARE @errorNumber INT, @errorSeverity INT, @errorState INT, @errorLine INT, @errorProcedure NVARCHAR(256), @errorMessage NVARCHAR(MAX), @task NVARCHAR(512)
DECLARE @BatchId INT, @Updated INT = 0, @Inserted INT = 0, @retval INT = -1, @err INT = 0, @startTranCount INT = @@TRANCOUNT, @source1WM DATETIME, @source1MaxWM DATETIME
DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT
DECLARE @isReconciled BIT = 1
SELECT @task = ''Inserting into Dimension''
INSERT INTO dbo.AlertSeverity (
EnumTypeId,
AlertSeverityValue,
Ordinal,
ParentId,
ID,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
NULL AS EnumTypeId,
source.Severity,
NULL AS Ordinal,
NULL AS ParentId,
NULL AS ID,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformAlertSeverityProc_Source1 source
LEFT JOIN dbo.AlertSeverity dest ON
CAST(source.Severity AS VARCHAR(32)) = dest.AlertSeverityValue
WHERE dest.AlertSeverityValue IS NULL
AND source.Severity IS NOT NULL