IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertCategoryProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertCategoryProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertCategoryProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
DECLARE @utc DATETIME = GETUTCDATE()
DECLARE @errorNumber INT, @errorCategory 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.AlertCategory (
EnumTypeId,
AlertCategoryValue,
Ordinal,
ParentId,
ID,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
NULL AS EnumTypeId,
source.Category,
NULL AS Ordinal,
NULL AS ParentId,
NULL AS ID,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformAlertCategoryProc_Source1 source
LEFT JOIN dbo.AlertCategory dest ON
source.Category = dest.AlertCategoryValue
WHERE dest.AlertCategoryValue IS NULL