--select distinct FileId, CustomerProcedureType, CustomerProcedureName, CustomerProcedureDescription from DL_Daily_ProcedureFile order by FileId desc

--select top 10 * from DL_Daily_ProcedureFile order by FileId desc


--select distinct CustomerProcedureName, CustomerProcedureDescription from DL_Daily_ProcedureFile
--select distinct CustomerProcedureDescription from DL_Daily_ProcedureFile

-- from ETL to DW - ProcedureType = ProcedureCategory (diag, interventional, meds etc)
-- from ETL to DW - ProcedureName = dim_ProcedureType (Echo, Cath, etc)
-- from ETL to DW - ProcedureDescription = dim_ProcedureName
-- from ETL to DW - <nothing> = dim_proceduredescription - dormant dimension

Begin
DECLARE @MyTableVar TABLE ( dim_ETLvalue nVARCHAR(200));

declare @fileid int = 58404
merge [HL_MONF_DATA].[dbo].[dim_ProcedureName] dim_PN
Using
(
	select distinct pf.CustomerProcedureDescription as ProcedureName from DL_Daily_ProcedureFile pf
				where 
				pf.CustomerProcedureDescription is not NULL	
				and	pf.FileId = @fileId
				--AND @listenerSubType = @constCSV
) Source
on
Source.ProcedureName = dim_PN.ETLMatchValue
when not matched then
insert (FriendlyName, StandardCode, Available, ETLMatchValue, ETLOutputKey)
values (Source.ProcedureName, null, 1, Source.ProcedureName, null)
output Inserted.ETLMatchValue into @MyTableVar;
select * from @MyTableVar
--select * from HL_MONF_Data.dbo.dim_ProcedureName
UPDATE
    HL_MONF_Data.dbo.dim_ProcedureName
SET
    ETLOutputKey = pn.Id
FROM
    HL_MONF_Data.dbo.dim_ProcedureName pn
INNER JOIN
    @MyTableVar RAN
ON 
    pn.ETLMatchValue = RAN.dim_ETLValue 
--where ETLOutputKey is null
End
Go

--truncate table HL_MONF_Data.dbo.dim_ProcedureName

select * from HL_MONF_Data.dbo.dim_ProcedureName