Single cache script

USE [ReportingSubscriptions]
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
 DROP TABLE #temp
IF OBJECT_ID('tempdb..#tempUncachedReports') IS NOT NULL
 DROP TABLE #tempUncachedReports
DECLARE @ReportPath varchar(max) = '/Italy/Strade_Province'
DECLARE @ReportDate datetime = dateadd(day,-1,getdate())
declare @ParameterName varchar(50)
declare @ParameterValue varchar(max)
declare @S varchar(max)
declare @R varchar(max)
declare @rw int
CREATE TABLE #tempUncachedReports
(
RowNo int
,ReportName varchar(1000)
,ParameterValue varchar(max)
)
CREATE TABLE #temp
(
 ReportName varchar(1000)
,ParameterName varchar(1000)
,ParameterValue varchar(max)
,RowNo int
)
/*Insert uncached not in report*/
INSERT INTO #tempUncachedReports
SELECT rank() over(order by ItemPath,ParameterValues) as rowNo,ItemPath,ParameterValues
from (
select distinct ItemPath,replace(replace(replace(replace(replace(cast([Parameters] as nvarchar(max)),'&','#'),'%5B','['),'%5D',']'),'%26','&'),'%20',' ') as ParameterValues
from [ReportServer].dbo.ExecutionLog3
where Timestart >= @ReportDate
and ItemPath like @ReportPath
--and format = 'RPL'
and Source = 'Live'
) as a

DECLARE @LoopCounter int = 1
DECLARE @MaxVal int = (select max(RowNo) from #tempUncachedReports)
WHILE (@LoopCounter <=@MaxVal)
BEGIN
    SET @R = (select ReportName from #tempUncachedReports where RowNo=@LoopCounter)
 SET @S = (select ParameterValue from #tempUncachedReports where RowNo=@LoopCounter)
 SET @rw = (select RowNo from #tempUncachedReports where RowNo=@LoopCounter)
 while len(@S) > 0
  begin
    set @ParameterName = left(@S, charindex('=', @S+'=')-1)
    set @ParameterValue = replace(left(@S, charindex('#', @S+'#')-1), @ParameterName + '=','')
    set @S = stuff(@S, 1, charindex('#', @S+'#'), '')
   
    insert into #temp
    (
    ReportName
    ,ParameterName
    ,ParameterValue
    ,RowNo
    )
    SELECT @R as ReportName,@ParameterName as ParameterName,@ParameterValue as Parameter,@rw as RowNo
  end
  SET @LoopCounter += 1
END
--select * from #temp
--select * from #tempUncachedReports
set @rw = (select max(RowNo) from #temp)
set @LoopCounter = 1
WHILE (@LoopCounter <=@MaxVal)
BEGIN
INSERT INTO [Italy].[ALD_Province]
           ([WholesalerWholesaler]
           ,[WholesalerSubGroup]
           ,[MarketCategory]
           ,[PackCategory]
           ,[GenericStatus]
           ,[VALUEUNITS]
           ,[Region]
           ,[PERIOD]
           ,[RANGE]
           ,[MICROBRICK]
           ,[Province]
           ,[MICROBRICKSelected]
           ,[PrescriptionStatus]
           ,[ReimbursementStatus]
           ,[Manufacturer]
           ,[ProductType]
           ,[Product]
           ,[ActiveFlag]
           ,[Ordering]
           ,[NewRow]
           ,[WID]
     )
select distinct
           WholesalerWholesaler
           ,WholesalerSubGroup
           ,MarketCategory
           ,PackCategory
           ,GenericStatus
           ,VALUEUNITS
           ,Region
           ,PERIOD
           ,[RANGE]
           ,MICROBRICK
           ,Province
           ,MICROBRICKSelected
           ,PrescriptionStatus
           ,ReimbursementStatus
           ,Manufacturer = null
           ,ProductType = null
           ,Product = null
           ,1 as ActiveFlag
           ,1 as Ordering
           ,1 as NewRow
           ,CONVERT(UNIQUEIDENTIFIER, WID) AS WID
from
(
  select ParameterValue as value, ParameterName as columnname
  from #temp
  WHERE RowNo = @LoopCounter
) d
pivot
(
  max(value)
  for columnname in (
  GenericStatus
  ,Manufacturer
  ,MarketCategory
  ,MICROBRICK
  ,MICROBRICKSelected
  ,PackCategory
  ,PERIOD
  ,PrescriptionStatus
  ,Product
  ,ProductType
  ,Province
  ,RANGE
  ,Region
  ,ReimbursementStatus
  ,VALUEUNITS
  ,WholesalerSubGroup
  ,WholesalerWholesaler
  ,WID
  )
) piv
where not exists(
select *
from [Italy].[ALD_Province] a
WHERE
a.WholesalerWholesaler = piv.WholesalerWholesaler
and isnull(a.WholesalerSubGroup,'') = isnull(piv.WholesalerSubGroup,'')
and isnull(a.MarketCategory,'') = isnull(piv.MarketCategory,'')
and isnull(a.PackCategory,'') = isnull(piv.PackCategory,'')
and isnull(a.GenericStatus,'') = isnull(piv.GenericStatus,'')
and isnull(a.VALUEUNITS,'') = isnull(piv.VALUEUNITS,'')
and isnull(a.Region,'') = isnull(piv.Region,'')
and isnull(a.PERIOD,'') = isnull(piv.PERIOD,'')
and isnull(a.[RANGE],'') = isnull(piv.[RANGE],'')
and isnull(a.MICROBRICK,'') = isnull(piv.MICROBRICK,'')
and isnull(a.Province,'') = isnull(piv.Province,'')
and isnull(a.MICROBRICKSelected,'') = isnull(piv.MICROBRICKSelected,'')
and isnull(a.PrescriptionStatus,'') = isnull(piv.PrescriptionStatus,'')
and isnull(a.ReimbursementStatus,'') = isnull(piv.ReimbursementStatus,'')
and isnull(a.Manufacturer,'') = isnull(piv.Manufacturer,'')
and isnull(a.ProductType,'') = isnull(piv.ProductType,'')
and isnull(a.Product,'') = isnull(piv.Product,'')
AND TRY_CONVERT(UNIQUEIDENTIFIER, piv.WID) is not null
)
--)
 SET @LoopCounter += 1
END

DROP TABLE #temp
DROP TABLE #tempUncachedReports