IF  EXISTS (SELECT
		distinct 1
	FROM [dbo].[Analytics$LSCentralColumnMetadata]
	WHERE [TABLE_NAME] like '%Item$437dbf0e-84ff-417a-965d-ed2bb9650972$ext' )
BEGIN
	DROP TABLE IF EXISTS #TEMPLSCentralColumnMetadata;

	WITH ShortTableName
	AS
	(SELECT
			TRANSLATE([SourceTableName], '."\/%][', '_______') AS [LongTableName]
		  ,TRANSLATE([PrefixedSourceTableName], '."\/%][', '_______') AS [Prefixed ShortTableName]
		FROM [dbo].[Analytics$SourceTablesMap]  
		GROUP BY [SourceTableName]
				,[PrefixedSourceTableName]),
	PublisherAffixReg
	AS
	(SELECT
			CAST([App ID] AS NVARCHAR(128)) AS [App ID]
		  ,[Prefix]
		  ,[Suffix]
		FROM [dbo].[Analytics$PublisherAffixReg]
		UNION
		SELECT
			'N/A' AS [App ID]
		  ,NULL AS [Prefix]
		  ,NULL AS [Suffix]),

	ShortenedColumnNames
	AS
	(SELECT
			LCM.[TABLE_NAME]
		  ,LCM.[COLUMN_NAME]
		  ,LCM.[DATA_TYPE]
		  ,LCM.[ORDINAL_POSITION]
		  ,LCM.[CHARACTER_MAXIMUM_LENGTH]
		  ,LCM.[NUMERIC_PRECISION]
		  ,LCM.[NUMERIC_SCALE]
		  ,LCM.[CountRows]
		  ,COALESCE(STN.[LongTableName], LCM.[BaseTableName]) AS [BaseTableName]
		  ,LCM.[TablePrefix]
		  ,LCM.[ExtensionGUID]
		  ,LCM.[Keycol]
		  ,LCM.Publisher
		FROM PublisherAffixReg PAR
		LEFT HASH JOIN [Analytics$LSCentralColumnMetadata] LCM
			ON PAR.[App ID] = LCM.[ExtensionGUID]
		LEFT JOIN ShortTableName STN
			ON LCM.BaseTableName = STN.[Prefixed ShortTableName]),

CleanColumnSuffix
AS
(
SELECT
		SCN.[TABLE_NAME]
	  ,SCN.[COLUMN_NAME] AS [Org_COLUMN_NAME]
	  ,PAR.Prefix
	  ,CASE	
			WHEN SCN.ExtensionGUID = 'EXT' and SCN.Keycol = 'FALSE' AND SCN.COLUMN_NAME <> 'timestamp'
				THEN TRIM(LEFT(SCN.[COLUMN_NAME], LEN(SCN.[COLUMN_NAME]) - 37))
			ELSE SCN.COLUMN_NAME
		END AS [COLUMN_NAME]
	  ,SCN.[DATA_TYPE]
	  ,SCN.[ORDINAL_POSITION]
	  ,SCN.[CHARACTER_MAXIMUM_LENGTH]
	  ,SCN.[NUMERIC_PRECISION]
	  ,SCN.[NUMERIC_SCALE]
	  ,SCN.[CountRows]
	  ,SCN.[BaseTableName]
	  ,SCN.[TablePrefix]
	  ,SCN.[ExtensionGUID]
	  ,SCN.[Keycol]
	  ,SCN.Publisher
	FROM ShortenedColumnNames SCN
	LEFT JOIN PublisherAffixReg PAR
		ON SCN.[ExtensionGUID] = PAR.[App ID]
	LEFT JOIN ShortTableName STN
		ON SCN.BaseTableName = STN.[Prefixed ShortTableName]),

CleanColumnNames
AS
(SELECT 
	[TABLE_NAME]
	,[Org_COLUMN_NAME]
	,[Prefix]
	,CASE
 WHEN [Publisher] NOT IN ('Microsoft', 'Company') AND
		LEFT([COLUMN_NAME], 5) = 'LSCHT' THEN TRIM(RIGHT([COLUMN_NAME], LEN([COLUMN_NAME]) - 5))	
		WHEN 
		LEFT([COLUMN_NAME], LEN([Prefix])) = [Prefix] THEN TRIM(RIGHT([COLUMN_NAME], LEN([COLUMN_NAME]) - LEN(Prefix)))	
		ELSE COLUMN_NAME
	END AS [COLUMN_NAME]
	,[DATA_TYPE]
	,[ORDINAL_POSITION]
	,[CHARACTER_MAXIMUM_LENGTH]
	,[NUMERIC_PRECISION]
	,[NUMERIC_SCALE]
	,[CountRows]
	,[BaseTableName]
	,[TablePrefix]
	,[ExtensionGUID]
	,[Keycol]
	,[Publisher]
	FROM CleanColumnSuffix),

	ColumnOrder
	AS
	(SELECT
			[TABLE_NAME]
		  ,[Org_COLUMN_NAME]
		  ,[COLUMN_NAME]
		  ,CASE
				WHEN Publisher NOT IN ('Microsoft', 'Company') AND
					Keycol = 'FALSE' AND
					COLUMN_NAME != 'timestamp' THEN DENSE_RANK() OVER (PARTITION BY [BaseTableName], [COLUMN_NAME] ORDER BY [ExtensionGUID])
				ELSE NULL
			END AS ColumnWithSameNameOrder
		FROM CleanColumnNames)
	SELECT
		cl.[TABLE_NAME]
	  ,cl.Org_COLUMN_NAME
	  ,CASE
			WHEN co.ColumnWithSameNameOrder > 1 THEN cl.[COLUMN_NAME] + '-duplicate-' + CAST(co.ColumnWithSameNameOrder AS VARCHAR(2))
			ELSE cl.[COLUMN_NAME]
		END AS [COLUMN_NAME]
	  ,cl.[DATA_TYPE]
	  ,cl.[ORDINAL_POSITION]
	  ,cl.[CHARACTER_MAXIMUM_LENGTH]
	  ,cl.[NUMERIC_PRECISION]
	  ,cl.[NUMERIC_SCALE]
	  ,cl.[CountRows]
	  ,cl.[BaseTableName]
	  ,cl.[TablePrefix]
	  ,cl.[ExtensionGUID]
	  ,cl.[Keycol] INTO #TEMPLSCentralColumnMetadata
	FROM CleanColumnNames cl
		,ColumnOrder co
	WHERE cl.TABLE_NAME = co.TABLE_NAME
	AND cl.Org_COLUMN_NAME = co.Org_COLUMN_NAME
	;
	WITH FilteredTableColumns
	AS
	(SELECT
		TablePrefix
	  ,BaseTableName
	  ,CountRows
	  ,Keycol
	  ,COLUMN_NAME
	  ,Org_COLUMN_NAME
	  ,ExtensionGUID
	  ,[TABLE_NAME]
	  ,ORDINAL_POSITION
	  ,DATA_TYPE
	  ,NUMERIC_SCALE
	  ,NUMERIC_PRECISION
	  ,CHARACTER_MAXIMUM_LENGTH
	  ,REPLACE(BaseTableName, ' ', '') + CAST(DENSE_RANK() OVER (PARTITION BY BaseTableName ORDER BY ExtensionGUID) AS VARCHAR(3)) AS Extno
	  ,DENSE_RANK() OVER (PARTITION BY BaseTableName, COLUMN_NAME ORDER BY ORDINAL_POSITION, ExtensionGUID) columntouse
	FROM #TEMPLSCentralColumnMetadata AS AllTableColumns
	INNER JOIN [Analytics$Companies] comp
		ON comp.CompanyPrefix = AllTableColumns.TablePrefix
	INNER JOIN [Analytics$SourceTablesMap] tabl
		ON tabl.SourceTableName = AllTableColumns.BaseTableName
	WHERE comp.IncludeCompany = 'TRUE'
	AND tabl.IncludeTable = 'TRUE'
		),

	-- Get the ordinal position over all extensions
	-- Get a table number
	-- Cast decimal types to decimal(28,10) 
	-- cast timestamp to bigint for incremental load
	-- create as type for each column type
	-- Create drop table statement
	-- Create destination Table name
	tt
	AS
	(SELECT --DISTINCT
			FilteredTableColumns.TablePrefix
		 ,FilteredTableColumns.BaseTableName
		 ,FilteredTableColumns.Extno
		 ,FilteredTableColumns.CountRows
		 ,DENSE_RANK() OVER (PARTITION BY FilteredTableColumns.BaseTableName ORDER BY FilteredTableColumns.ExtensionGUID, FilteredTableColumns.ORDINAL_POSITION) extOrdinalpos
		 ,FilteredTableColumns.Keycol
		 ,CASE FilteredTableColumns.Keycol
				WHEN 'TRUE' THEN FilteredTableColumns.Extno + '.[' + FilteredTableColumns.COLUMN_NAME + ']'
				ELSE ''
			END AS KeyJoinColumn
		 ,FilteredTableColumns.ExtensionGUID
		 ,CAST(FilteredTableColumns.[TABLE_NAME] AS NVARCHAR(200)) AS TableName
			,FilteredTableColumns.COLUMN_NAME
			,FilteredTableColumns.Org_COLUMN_NAME
		 ,DENSE_RANK() OVER (ORDER BY FilteredTableColumns.TABLE_NAME) AS TableNo
		 ,FilteredTableColumns.ORDINAL_POSITION
	 ,CASE FilteredTableColumns.DATA_TYPE
				WHEN 'decimal' THEN ' CAST(' + FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS decimal(28,10)) AS [' + FilteredTableColumns.COLUMN_NAME + ']'
				WHEN 'bigint' THEN ' ' + FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS [bigint_' + FilteredTableColumns.COLUMN_NAME + ']'
				ELSE FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS [' + FilteredTableColumns.COLUMN_NAME + ']'
			END AS caststatus
			,CASE FilteredTableColumns.DATA_TYPE
				WHEN 'bigint' THEN FilteredTableColumns.Extno + '.[' + FilteredTableColumns.COLUMN_NAME + '] '
				ELSE NULL
			END AS Incrementalbase
		 ,CASE DATA_TYPE
				WHEN 'nvarchar' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
				WHEN 'varchar' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
				WHEN 'int' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				WHEN 'decimal' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(FilteredTableColumns.NUMERIC_SCALE AS VARCHAR(10)) + ')'
				WHEN 'datetime' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				WHEN 'timestamp' THEN '[bigint_' + FilteredTableColumns.COLUMN_NAME + '] [Bigint]'
				WHEN 'bigint' THEN '[bigint_' + FilteredTableColumns.COLUMN_NAME + '] [Bigint]'
				WHEN 'uniqueidentifier' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				ELSE '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
			END Createstatus
		 ,'DROP TABLE [stg' + BaseTableName + ']' AS DropTableScript
		 ,'[stg ' + BaseTableName + ']' AS DestTableName
		FROM FilteredTableColumns
		GROUP BY
		FilteredTableColumns.TablePrefix
		 ,FilteredTableColumns.BaseTableName
		 ,FilteredTableColumns.Extno
		 ,FilteredTableColumns.CountRows
		 ,FilteredTableColumns.ExtensionGUID, FilteredTableColumns.ORDINAL_POSITION
		 ,FilteredTableColumns.Keycol
		 ,FilteredTableColumns.[TABLE_NAME]
		 ,FilteredTableColumns.COLUMN_NAME
		 ,FilteredTableColumns.Org_COLUMN_NAME
		 ,FilteredTableColumns.DATA_TYPE
		 ,FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH
		 ,FilteredTableColumns.NUMERIC_PRECISION
		 ,FilteredTableColumns.NUMERIC_SCALE
	)


	-- Get the base list of the source tables 
	,BaseTable
	AS
	(SELECT 
			BaseTableName
		 ,'[' + TableName + '] ' + Extno AS TableName
		 ,TableNo, TablePrefix, CountRows
		FROM tt
		GROUP BY
		BaseTableName,TableName,Extno,TableNo, TablePrefix, CountRows)

	-- Get the Columns for each source table for the Select Statement
	, BaseColumns AS ( 
	SELECT 
			BaseTableName
			,CASE WHEN tt.COLUMN_NAME = 'timestamp' then MIN(Incrementalbase) ELSE NULL END as IncrementalKey
		 ,MIN(caststatus) as caststatus
		 ,MIN(TableNo) as TableNo
		 ,MIN(ORDINAL_POSITION) as ORDINAL_POSITION
		 , TablePrefix
		FROM tt
		GROUP BY
		BaseTableName, TablePrefix, COLUMN_NAME
	)

	-- Get the Columns for each source table for the create statement
		, BaseCreateColumns AS ( 
	SELECT 
			BaseTableName, COLUMN_NAME
		 ,MIN(Createstatus) as Createstatus
		 ,MIN(TableNo) as TableNo
		 ,MIN(ORDINAL_POSITION) as ORDINAL_POSITION
		 , TablePrefix
		FROM tt
		GROUP BY
		BaseTableName, TablePrefix, COLUMN_NAME
	)

	-- Get the Primary key columns for each source table for the join statement
	,BaseKeyColumns AS (
	SELECT
			BaseTableName
		 ,KeyJoinColumn
		 ,TableNo, ORDINAL_POSITION, TablePrefix
		FROM tt

	)
	-- get a list of the source tables in a string (all extension in one string)
	,Tablelists
	AS
	(SELECT
			BaseTable.BaseTableName,max(CountRows) AS CountRows, TablePrefix 
		 ,STRING_AGG(CAST(BaseTable.TableName + ' WITH (NOLOCK)' AS NVARCHAR(MAX)), ' ,')
		 Within GROUP (Order By TableNo asc ) AS TableSnippet
		FROM BaseTable
		GROUP BY BaseTable.BaseTableName, TablePrefix
		)

	-- get a list of the columns for each extension in a string (all extension columns in one string)
	,Columnlists AS (
	SELECT
			BaseColumns.BaseTableName, TablePrefix, max(IncrementalKey) IncrementalKey
		 ,STRING_AGG(CAST(BaseColumns.caststatus AS NVARCHAR(MAX)), ' ,')
		 Within GROUP (Order By TableNo, ORDINAL_POSITION asc )AS ColumnSnippet
		FROM BaseColumns
		GROUP BY BaseColumns.BaseTableName, TablePrefix

	)
	-- get a list of the JOIN columns for each extension in a string - alll extensions join the base table (all joins in one string for each base table)
	,JoinTablelist AS
	(SELECT
		BaseTableName
		,COLUMN_NAME
		,REPLACE(BaseTableName, ' ', '') + '1.[' + COLUMN_NAME +']' as BaseJoinCol 
	 ,COUNT(KeyJoinColumn) as CountJoins
	 ,TablePrefix
	FROM tt
	where Keycol = 'TRUE'
	group by 
	BaseTableName
		,COLUMN_NAME ,TablePrefix
	)
	--List the Primary Keys for each base table
	,KeyJoinListString AS(
	SELECT
		JoinTablelist.BaseTableName
	 ,JoinTablelist.BaseJoinCol + ' = ' + tt.KeyJoinColumn as KeyJoinString
	 ,TableNo
	 ,ORDINAL_POSITION
	 ,JoinTablelist.TablePrefix
	FROM JoinTablelist
	INNER JOIN tt
	ON 
	JoinTablelist.BaseTableName = tt.BaseTableName
	and JoinTablelist.TablePrefix = tt.TablePrefix
	and JoinTablelist.COLUMN_NAME = tt.COLUMN_NAME
	and tt.KeyJoinColumn <> ''
	)
	-- get a list of the join statement for each extension in a string (all extension joins in one string)
	,Joinlists AS (
	SELECT
			KeyJoinListString.BaseTableName, KeyJoinListString.TablePrefix
		 ,'WHERE ' + STRING_AGG(CAST(KeyJoinListString.KeyJoinString AS NVARCHAR(MAX)), ' AND ')
		 Within GROUP (Order By TableNo, ORDINAL_POSITION asc )AS JoinSnippet
		FROM KeyJoinListString
		GROUP BY KeyJoinListString.BaseTableName, KeyJoinListString.TablePrefix
		)
	--Generate the Create table snippet
	,CreateTableList AS(
	Select BaseTableName, TablePrefix
	,'CREATE TABLE [stg$'+ BaseTableName + '] ([CompanyPrefix] [nvarchar] (200), ' + STRING_AGG(CAST(Createstatus AS NVARCHAR(MAX))
	, ' ,') WITHIN GROUP (order by TableNo, ORDINAL_POSITION asc ) +' , [ADF Run GUID] [nvarchar](100) NULL) ON [PRIMARY]; '
	+'CREATE NonClustered INDEX [StagingIndex'+ BaseTableName + '] ON [dbo].[stg$'+ BaseTableName + '] ([CompanyPrefix] ASC, [bigint_timestamp] ASC)'
	 AS CreateSnippet
	from BaseCreateColumns
	Group by BaseTableName, TablePrefix
	)
	--Generate the Drop table snippet
	,DropTableList AS(
	Select BaseTableName, TablePrefix
	,'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''stg$'+ BaseTableName + ''' ) AND type in (''U'')) DROP TABLE [stg$'+ BaseTableName + '] ' DropSnippet
	from BaseCreateColumns
	Group by BaseTableName, TablePrefix
	)
	--Assemble the results in one result array
	SELECT 
		Tablelists.BaseTableName
		,Tablelists.CountRows
		,SUM(Tablelists.CountRows) OVER (Partition BY Tablelists.BaseTableName) as SumCountRows
	,COUNT(Tablelists.CountRows) OVER (Partition BY Tablelists.BaseTableName) as CountCompanies
	,RANK() OVER(Partition BY Tablelists.BaseTableName ORDER BY Tablelists.TablePrefix) AS CompanyNumber
		,Tablelists.TablePrefix
		, 'stg$'+ Tablelists.BaseTableName As DestinationTable
	 ,ColumnSnippet
	 ,TableSnippet
	 ,JoinSnippet
	 ,'SELECT ' + ColumnSnippet + ' FROM ' + TableSnippet + ' ' + JoinSnippet AS SelectStatement
	 ,DropSnippet
	 ,CreateSnippet
	 ,DropSnippet + ' ' + CreateSnippet as DropAndCreate
	 ,IncrementalKey
	FROM Tablelists 
	JOIN Columnlists 
	ON
	Columnlists.BaseTableName = Tablelists.BaseTableName
	AND Columnlists.TablePrefix = Tablelists.TablePrefix
	JOIN Joinlists
	ON Columnlists.BaseTableName = Joinlists.BaseTableName
	AND Columnlists.TablePrefix = Joinlists.TablePrefix
	JOIN CreateTableList
	ON Columnlists.BaseTableName = CreateTableList.BaseTableName
	AND Columnlists.TablePrefix = CreateTableList.TablePrefix
	JOIN DropTableList
	ON Columnlists.BaseTableName = DropTableList.BaseTableName
	AND Columnlists.TablePrefix = DropTableList.TablePrefix
	--CLEAN Temp table
	DROP TABLE IF EXISTS #TEMPLSCentralColumnMetadata;
END
ELSE
	BEGIN
		DROP TABLE IF EXISTS #TEMPLSCentralColumnMetadataV22;
	WITH ShortTableName
	AS
	(SELECT
			TRANSLATE([SourceTableName], '."\/%][', '_______') AS [LongTableName]
		  ,TRANSLATE([PrefixedSourceTableName], '."\/%][', '_______') AS [Prefixed ShortTableName]
		FROM [dbo].[Analytics$SourceTablesMap]  --[Analytics$ShortTableNameMap]
		GROUP BY [SourceTableName]
		,[PrefixedSourceTableName]),
	PublisherAffixReg
	AS
	(SELECT
		CAST([App ID] AS NVARCHAR(128)) AS [App ID]
		,[Prefix]
		,[Suffix]
		FROM [dbo].[Analytics$PublisherAffixReg]
		UNION
		SELECT
		'N/A' AS [App ID]
		,NULL AS [Prefix]
		,NULL AS [Suffix]),

	ShortenedColumnNames
	AS
	(SELECT
			LCM.[TABLE_NAME]
		 ,LCM.[COLUMN_NAME]
		 ,LCM.[DATA_TYPE]
		 ,LCM.[ORDINAL_POSITION]
		 ,LCM.[CHARACTER_MAXIMUM_LENGTH]
		 ,LCM.[NUMERIC_PRECISION]
		 ,LCM.[NUMERIC_SCALE]
		 ,LCM.[CountRows]
		 ,COALESCE(STN.[LongTableName], LCM.[BaseTableName]) AS [BaseTableName]
		 ,LCM.[TablePrefix]
		 ,LCM.[ExtensionGUID]
		 ,LCM.[Keycol]
		 ,LCM.Publisher
		FROM PublisherAffixReg PAR 
		LEFT HASH JOIN [Analytics$LSCentralColumnMetadata] LCM
			ON PAR.[App ID] = LCM.[ExtensionGUID] 
		LEFT JOIN ShortTableName STN
			ON LCM.BaseTableName = STN.[Prefixed ShortTableName]),

	CleanColumnNames
	AS
	(SELECT
			SCN.[TABLE_NAME]
		 ,SCN.[COLUMN_NAME] AS [Org_COLUMN_NAME]
		 ,CASE
				WHEN 
					LEFT(SCN.[COLUMN_NAME], LEN(PAR.Prefix)) = PAR.Prefix THEN TRIM(RIGHT(SCN.[COLUMN_NAME], LEN(SCN.[COLUMN_NAME]) - LEN(PAR.Prefix)))
				WHEN SCN.Publisher NOT IN ('Microsoft', 'Company') AND
					RIGHT(SCN.[COLUMN_NAME], LEN(PAR.Suffix)) = PAR.Suffix THEN TRIM(LEFT(SCN.[COLUMN_NAME], LEN(SCN.[COLUMN_NAME]) - LEN(PAR.Suffix)))
				ELSE SCN.[COLUMN_NAME]
			END AS [COLUMN_NAME]
		 ,SCN.[DATA_TYPE]
		 ,SCN.[ORDINAL_POSITION]
		 ,SCN.[CHARACTER_MAXIMUM_LENGTH]
		 ,SCN.[NUMERIC_PRECISION]
		 ,SCN.[NUMERIC_SCALE]
		 ,SCN.[CountRows]
		 ,SCN.[BaseTableName] 
		 ,SCN.[TablePrefix]
		 ,SCN.[ExtensionGUID]
		 ,SCN.[Keycol]
		 ,SCN.Publisher
		FROM ShortenedColumnNames SCN
		LEFT JOIN PublisherAffixReg PAR
			ON SCN.[ExtensionGUID] = PAR.[App ID]
		LEFT JOIN ShortTableName STN
			ON SCN.BaseTableName = STN.[Prefixed ShortTableName]),

	ColumnOrder
	AS
	(SELECT
			[TABLE_NAME]
		 ,[Org_COLUMN_NAME]
		 ,[COLUMN_NAME]
		 ,CASE
				WHEN Publisher NOT IN ('Microsoft', 'Company') AND
					Keycol = 'FALSE' AND
					COLUMN_NAME != 'timestamp' THEN DENSE_RANK() OVER (PARTITION BY [BaseTableName], [COLUMN_NAME] ORDER BY [ExtensionGUID])
				ELSE NULL
			END AS ColumnWithSameNameOrder
		FROM CleanColumnNames)
	SELECT
		cl.[TABLE_NAME]
	 ,cl.Org_COLUMN_NAME
	 ,CASE
			WHEN co.ColumnWithSameNameOrder > 1 THEN cl.[COLUMN_NAME] + '-duplicate-' + CAST(co.ColumnWithSameNameOrder AS VARCHAR(2))
			ELSE cl.[COLUMN_NAME]
		END AS [COLUMN_NAME]
	 ,cl.[DATA_TYPE]
	 ,cl.[ORDINAL_POSITION]
	 ,cl.[CHARACTER_MAXIMUM_LENGTH]
	 ,cl.[NUMERIC_PRECISION]
	 ,cl.[NUMERIC_SCALE]
	 ,cl.[CountRows]
	 ,cl.[BaseTableName]
	 ,cl.[TablePrefix]
	 ,cl.[ExtensionGUID]
	 ,cl.[Keycol] INTO #TEMPLSCentralColumnMetadataV22
	FROM CleanColumnNames cl
		,ColumnOrder co
	WHERE cl.TABLE_NAME = co.TABLE_NAME
	AND cl.Org_COLUMN_NAME = co.Org_COLUMN_NAME
	;
	WITH FilteredTableColumns
	AS
	(SELECT
			TablePrefix,BaseTableName,CountRows,Keycol,COLUMN_NAME,Org_COLUMN_NAME,ExtensionGUID,[TABLE_NAME],ORDINAL_POSITION,DATA_TYPE,NUMERIC_SCALE,NUMERIC_PRECISION,CHARACTER_MAXIMUM_LENGTH
		 ,REPLACE(BaseTableName, ' ', '') + CAST(DENSE_RANK() OVER (PARTITION BY BaseTableName ORDER BY ExtensionGUID) AS VARCHAR(3)) AS Extno
		 ,DENSE_RANK() OVER (PARTITION BY BaseTableName, COLUMN_NAME ORDER BY ORDINAL_POSITION, ExtensionGUID) columntouse
		FROM #TEMPLSCentralColumnMetadataV22 AS AllTableColumns
		inner Join [Analytics$Companies] comp
		ON comp.CompanyPrefix = AllTableColumns.TablePrefix
		inner join [Analytics$SourceTablesMap] tabl
		ON tabl.SourceTableName = AllTableColumns.BaseTableName
		where comp.IncludeCompany = 'TRUE'
		AND tabl.IncludeTable = 'TRUE'
		),

	-- Get the ordinal position over all extensions
	-- Get a table number
	-- Cast decimal types to decimal(28,10) 
	-- cast timestamp to bigint for incremental load
	-- create as type for each column type
	-- Create drop table statement
	-- Create destination Table name
	tt
	AS
	(SELECT --DISTINCT
			FilteredTableColumns.TablePrefix
		 ,FilteredTableColumns.BaseTableName
		 ,FilteredTableColumns.Extno
		 ,FilteredTableColumns.CountRows
		 ,DENSE_RANK() OVER (PARTITION BY FilteredTableColumns.BaseTableName ORDER BY FilteredTableColumns.ExtensionGUID, FilteredTableColumns.ORDINAL_POSITION) extOrdinalpos
		 ,FilteredTableColumns.Keycol
		 ,CASE FilteredTableColumns.Keycol
				WHEN 'TRUE' THEN FilteredTableColumns.Extno + '.[' + FilteredTableColumns.COLUMN_NAME + ']'
				ELSE ''
			END AS KeyJoinColumn
		 ,FilteredTableColumns.ExtensionGUID
		 ,CAST(FilteredTableColumns.[TABLE_NAME] AS NVARCHAR(200)) AS TableName
			,FilteredTableColumns.COLUMN_NAME
			,FilteredTableColumns.Org_COLUMN_NAME
		 ,DENSE_RANK() OVER (ORDER BY FilteredTableColumns.TABLE_NAME) AS TableNo
		 ,FilteredTableColumns.ORDINAL_POSITION
	 ,CASE FilteredTableColumns.DATA_TYPE
				WHEN 'decimal' THEN ' CAST(' + FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS decimal(28,10)) AS [' + FilteredTableColumns.COLUMN_NAME + ']'
				WHEN 'bigint' THEN ' ' + FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS [bigint_' + FilteredTableColumns.COLUMN_NAME + ']'
				ELSE FilteredTableColumns.Extno + '.[' + FilteredTableColumns.Org_COLUMN_NAME + '] AS [' + FilteredTableColumns.COLUMN_NAME + ']'
			END AS caststatus
			,CASE FilteredTableColumns.DATA_TYPE
				WHEN 'bigint' THEN FilteredTableColumns.Extno + '.[' + FilteredTableColumns.COLUMN_NAME + '] '
				ELSE NULL
			END AS Incrementalbase
		 ,CASE DATA_TYPE
				WHEN 'nvarchar' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
				WHEN 'varchar' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
				WHEN 'int' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				WHEN 'decimal' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + '] (' + CAST(FilteredTableColumns.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(FilteredTableColumns.NUMERIC_SCALE AS VARCHAR(10)) + ')'
				WHEN 'datetime' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				WHEN 'timestamp' THEN '[bigint_' + FilteredTableColumns.COLUMN_NAME + '] [Bigint]'
				WHEN 'bigint' THEN '[bigint_' + FilteredTableColumns.COLUMN_NAME + '] [Bigint]'
				WHEN 'uniqueidentifier' THEN '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
				ELSE '[' + FilteredTableColumns.COLUMN_NAME + '] [' + FilteredTableColumns.DATA_TYPE + ']'
			END Createstatus
		 ,'DROP TABLE [stg' + BaseTableName + ']' AS DropTableScript
		 ,'[stg ' + BaseTableName + ']' AS DestTableName
		FROM FilteredTableColumns
		GROUP BY
		FilteredTableColumns.TablePrefix
		 ,FilteredTableColumns.BaseTableName
		 ,FilteredTableColumns.Extno
		 ,FilteredTableColumns.CountRows
		 ,FilteredTableColumns.ExtensionGUID, FilteredTableColumns.ORDINAL_POSITION
		 ,FilteredTableColumns.Keycol
		 ,FilteredTableColumns.[TABLE_NAME]
		 ,FilteredTableColumns.COLUMN_NAME
		 ,FilteredTableColumns.Org_COLUMN_NAME
		 ,FilteredTableColumns.DATA_TYPE
		 ,FilteredTableColumns.CHARACTER_MAXIMUM_LENGTH
		 ,FilteredTableColumns.NUMERIC_PRECISION
		 ,FilteredTableColumns.NUMERIC_SCALE
	)


	-- Get the base list of the source tables 
	,BaseTable
	AS
	(SELECT 
			BaseTableName
		 ,'[' + TableName + '] ' + Extno AS TableName
		 ,TableNo, TablePrefix, CountRows
		FROM tt
		GROUP BY
		BaseTableName,TableName,Extno,TableNo, TablePrefix, CountRows)

	-- Get the Columns for each source table for the Select Statement
	, BaseColumns AS ( 
	SELECT 
			BaseTableName
			,CASE WHEN tt.COLUMN_NAME = 'timestamp' then MIN(Incrementalbase) ELSE NULL END as IncrementalKey
		 ,MIN(caststatus) as caststatus
		 ,MIN(TableNo) as TableNo
		 ,MIN(ORDINAL_POSITION) as ORDINAL_POSITION
		 , TablePrefix
		FROM tt
		GROUP BY
		BaseTableName, TablePrefix, COLUMN_NAME
	)

	-- Get the Columns for each source table for the create statement
		, BaseCreateColumns AS ( 
	SELECT 
			BaseTableName, COLUMN_NAME
		 ,MIN(Createstatus) as Createstatus
		 ,MIN(TableNo) as TableNo
		 ,MIN(ORDINAL_POSITION) as ORDINAL_POSITION
		 , TablePrefix
		FROM tt
		GROUP BY
		BaseTableName, TablePrefix, COLUMN_NAME
	)

	-- Get the Primary key columns for each source table for the join statement
	,BaseKeyColumns AS (
	SELECT
			BaseTableName
		 ,KeyJoinColumn
		 ,TableNo, ORDINAL_POSITION, TablePrefix
		FROM tt

	)
	-- get a list of the source tables in a string (all extension in one string)
	,Tablelists
	AS
	(SELECT
			BaseTable.BaseTableName,max(CountRows) AS CountRows, TablePrefix 
		 ,STRING_AGG(CAST(BaseTable.TableName + ' WITH (NOLOCK)' AS NVARCHAR(MAX)), ' ,')
		 Within GROUP (Order By TableNo asc ) AS TableSnippet
		FROM BaseTable
		GROUP BY BaseTable.BaseTableName, TablePrefix
		)

	-- get a list of the columns for each extension in a string (all extension columns in one string)
	,Columnlists AS (
	SELECT
			BaseColumns.BaseTableName, TablePrefix, max(IncrementalKey) IncrementalKey
		 ,STRING_AGG(CAST(BaseColumns.caststatus AS NVARCHAR(MAX)), ' ,')
		 Within GROUP (Order By TableNo, ORDINAL_POSITION asc )AS ColumnSnippet
		FROM BaseColumns
		GROUP BY BaseColumns.BaseTableName, TablePrefix

	)
	-- get a list of the JOIN columns for each extension in a string - alll extensions join the base table (all joins in one string for each base table)
	,JoinTablelist AS
	(SELECT
		BaseTableName
		,COLUMN_NAME
		,REPLACE(BaseTableName, ' ', '') + '1.[' + COLUMN_NAME +']' as BaseJoinCol 
	 ,COUNT(KeyJoinColumn) as CountJoins
	 ,TablePrefix
	FROM tt
	where Keycol = 'TRUE'
	group by 
	BaseTableName
		,COLUMN_NAME ,TablePrefix
	)
	--List the Primary Keys for each base table
	,KeyJoinListString AS(
	SELECT
		JoinTablelist.BaseTableName
	 ,JoinTablelist.BaseJoinCol + ' = ' + tt.KeyJoinColumn as KeyJoinString
	 ,TableNo
	 ,ORDINAL_POSITION
	 ,JoinTablelist.TablePrefix
	FROM JoinTablelist
	INNER JOIN tt
	ON 
	JoinTablelist.BaseTableName = tt.BaseTableName
	and JoinTablelist.TablePrefix = tt.TablePrefix
	and JoinTablelist.COLUMN_NAME = tt.COLUMN_NAME
	and tt.KeyJoinColumn <> ''
	)
	-- get a list of the join statement for each extension in a string (all extension joins in one string)
	,Joinlists AS (
	SELECT
			KeyJoinListString.BaseTableName, KeyJoinListString.TablePrefix
		 ,'WHERE ' + STRING_AGG(CAST(KeyJoinListString.KeyJoinString AS NVARCHAR(MAX)), ' AND ')
		 Within GROUP (Order By TableNo, ORDINAL_POSITION asc )AS JoinSnippet
		FROM KeyJoinListString
		GROUP BY KeyJoinListString.BaseTableName, KeyJoinListString.TablePrefix
		)
	--Generate the Create table snippet
	,CreateTableList AS(
	Select BaseTableName, TablePrefix
	,'CREATE TABLE [stg$'+ BaseTableName + '] ([CompanyPrefix] [nvarchar] (200), ' + STRING_AGG(CAST(Createstatus AS NVARCHAR(MAX))
	, ' ,') WITHIN GROUP (order by TableNo, ORDINAL_POSITION asc ) +' , [ADF Run GUID] [nvarchar](100) NULL) ON [PRIMARY]; '
	+'CREATE NonClustered INDEX [StagingIndex'+ BaseTableName + '] ON [dbo].[stg$'+ BaseTableName + '] ([CompanyPrefix] ASC, [bigint_timestamp] ASC)'
	 AS CreateSnippet
	from BaseCreateColumns
	Group by BaseTableName, TablePrefix
	)
	--Generate the Drop table snippet
	,DropTableList AS(
	Select BaseTableName, TablePrefix
	,'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''stg$'+ BaseTableName + ''' ) AND type in (''U'')) DROP TABLE [stg$'+ BaseTableName + '] ' DropSnippet
	from BaseCreateColumns
	Group by BaseTableName, TablePrefix
	)
	--Assemble the results in one result array
	SELECT 
		Tablelists.BaseTableName
		,Tablelists.CountRows
		,SUM(Tablelists.CountRows) OVER (Partition BY Tablelists.BaseTableName) as SumCountRows
	,COUNT(Tablelists.CountRows) OVER (Partition BY Tablelists.BaseTableName) as CountCompanies
	,RANK() OVER(Partition BY Tablelists.BaseTableName ORDER BY Tablelists.TablePrefix) AS CompanyNumber
		,Tablelists.TablePrefix
		, 'stg$'+ Tablelists.BaseTableName As DestinationTable
	 ,ColumnSnippet
	 ,TableSnippet
	 ,JoinSnippet
	 ,'SELECT ' + ColumnSnippet + ' FROM ' + TableSnippet + ' ' + JoinSnippet AS SelectStatement
	 ,DropSnippet
	 ,CreateSnippet
	 ,DropSnippet + ' ' + CreateSnippet as DropAndCreate
	 ,IncrementalKey
	FROM Tablelists 
	JOIN Columnlists 
	ON
	Columnlists.BaseTableName = Tablelists.BaseTableName
	AND Columnlists.TablePrefix = Tablelists.TablePrefix
	JOIN Joinlists
	ON Columnlists.BaseTableName = Joinlists.BaseTableName
	AND Columnlists.TablePrefix = Joinlists.TablePrefix
	JOIN CreateTableList
	ON Columnlists.BaseTableName = CreateTableList.BaseTableName
	AND Columnlists.TablePrefix = CreateTableList.TablePrefix
	JOIN DropTableList
	ON Columnlists.BaseTableName = DropTableList.BaseTableName
	AND Columnlists.TablePrefix = DropTableList.TablePrefix
	--CLEAN Temp table
	DROP TABLE IF EXISTS #TEMPLSCentralColumnMetadataV22;
END;