if not exists (select * from sysobjects where type = 'P' AND name = N'TransformManagementPackDimProc')
begin
execute ('create procedure dbo.[TransformManagementPackDimProc] @WaterMark xml as return 1')
end
go
DECLARE @Statement NVARCHAR(MAX)
SET @Statement=
N'alter procedure dbo.TransformManagementPackDimProc (@WaterMark xml)
as
begin
set nocount on
DECLARE @utc DATETIME = GETUTCDATE()
-- Get Metadata
declare @BatchId int, @Updated int = 0, @Inserted int = 0, @ModuleName nvarchar(128),
@source1WM datetime, @source1MaxWM datetime, @retval int, @err int, @nullguid uniqueidentifier
set @nullguid = ''00000000-0000-0000-0000-000000000000''
;with MPList(ManagementPackId, MPIsSealed, MPName, MPVersion, MPKeyToken, DataSourceId, MPCreated, MPLastModified, RowId) as
(
select ManagementPackId, MPIsSealed, MPName, MPVersion, MPKeyToken,
DataSourceId, MPCreated, MPLastModified,
ROW_NUMBER() over (partition by DataSourceId, ManagementPackId order by MPLastMOdified desc) as RowId
from inbound.ManagementPackStaging
where DWTimeStamp >= @source1WM
and DWTimeStamp < @source1MaxWM
)
select *
into #temp1
from MPList
where RowId = 1
merge dbo.ManagementPackDim AS target
using #temp1 as source
on (target.ManagementPackId = source.ManagementPackId)
and (target.SourceId = source.DataSourceId)
when matched then
update set
MPIsSealed= isnull(source.MPIsSealed,0),
MPName = source.MPName,
MPVersion = source.MPVersion,
MPKeyToken = source.MPKeyToken,
TimeAdded = source.MPCreated,
LastModified = source.MPLastModified,
UpdatedBatchId = @BatchId,
@Updated = @Updated + 1
when not matched
by target then
insert (ManagementPackId, BaseManagedEntityId, MPIsSealed, MPName, MPVersion, MPKeyToken,
SourceId, TimeAdded, LastModified,UpdatedBatchId, InsertedBatchId, IsDeleted)
values (source.ManagementPackId, source.ManagementPackId, source.MPIsSealed, source.MPName, source.MPVersion, source.MPKeyToken,
source.DataSourceId, source.MPCreated, source.MPLastModified, 0, @BatchId, 0);