--USE DiabloSynch
GO
RETURN
DROP TABLE #TableOrder
IF OBJECT_ID('tempdb..#TableOrder') IS NULL
SELECT REPLACE(fqn, '[DiabloLoad].[db015_14_', '[DiabloStg-DiabloLoad].[DataFolder-') AS FQN,
ROW_NUMBER() OVER (ORDER BY ROWS DESC) AS ROW
INTO #TableOrder
FROM Diablostg.Metadata.TableUsage
WHERE tablename LIKE 'db015_14%'
AND tablename NOT LIKE '%trans'
AND tablename NOT LIKE '%mtg_sec_cat_cd'
AND tablename IN (SELECT REPLACE(ObjectName, 'datafolder-', 'db015_14_') AS TableName
FROM Diablo.Metadata.Indexes
WHERE schemaname = 'diablostg-diabloload'
AND objectname LIKE 'DataFolder-t%'
AND indexid = 1
AND keycnt = 4)
ORDER BY ROWS DESC
GO
IF OBJECT_ID('tempdb..#TableOrder') IS NULL
SELECT [FQN],
[ROW]
INTO #TableOrder
FROM ( VALUES ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_deed_sec_cat_cd]', 1), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sit_grp_info_01]', 2),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_nm_grp]', 3), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_addr]', 4),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_lot_info_grp]', 5), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_grp_info_01]', 6),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_orig_nm]', 7), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_01]', 8),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_01_legal]', 9), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_nm_grp]', 10),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_orig_nm]', 11), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sls_reject_grp_info_01]', 12),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_comment_txt]', 13), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_01_legal_addr]', 14),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_tax_exmp_grp_info_01]', 15), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sq_ft_grp_info_01]', 16),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sit_dpvnote]', 17), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_grp_info_01]', 18),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sls_comment_txt]', 19), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_orig_addr]', 20),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_addr_dpvnote]', 21), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_dist_tax_grp]', 22),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sls_notes_txt]', 23), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sls_legal]', 24),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_01_comment]', 25), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_cd_tabl_grp]', 26),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_cd_tabl_grp]', 27), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_buy_co_nm]', 28),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_02]', 29), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_addr]', 30),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_orig_addr]', 31), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_mtg_grp_info_01_int_rt]', 32),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_sel_co_nm]', 33), ( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_prin_addr]', 34),
( '[DiabloStg-DiabloLoad].[DataFolder-trans_t_bld_comment_txt]', 35) ) AS TableOrder ([FQN], [ROW])
RETURN
SELECT FQN,
SchemaName,
ObjectName,
KeyCnt,
KeyColumns
FROM Metadata.Indexes
WHERE schemaname = 'diablostg-diabloload'
AND objectname LIKE 'DataFolder-t%'
AND indexid = 1
--AND keycnt = 4
SELECT SchemaName,
ObjectName,
KeyCnt,
KeyColumns
FROM Metadata.Indexes
WHERE schemaname = 'diablostg-diabloload'
AND indexid = 1
AND objectname LIKE 'DataFolder-t%'
ORDER BY KeyCnt DESC,
KeyColumns,
objectname
go
-- IF OBJECT_ID('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns
IF OBJECT_ID('tempdb..#Columns') IS NOT NULL
IF NOT EXISTS ( SELECT *
FROM #Columns )
DROP TABLE #Columns
IF OBJECT_ID('tempdb..#Columns') IS NULL
SELECT c.FQN,
CASE WHEN columnname = 'Count1Max' THEN 1
ELSE 0
END AS Count1Max,
CASE WHEN columnname = 'Count1Max' THEN 'Count1Max_' + REPLACE(objectname, 'DataFolder-trans_t_', '')
ELSE columnname
END AS ColumnName,
rd.ROW,
't' + CAST(rd.ROW AS VARCHAR) AS ALIAS
INTO #Columns
FROM Metadata.Columns c
LEFT OUTER JOIN #TableOrder rd ON rd.FQN = c.FQN
WHERE ObjectId IN (SELECT objectid
FROM Metadata.Indexes
WHERE schemaname = 'diablostg-diabloload'
AND indexid = 1
AND objectname LIKE 'DataFolder-t%'
AND KeyColumns = '[CNTY_CD], [T_BATCH_DT], [T_BATCH_SEQ], [Count1]')
AND pkordinal IS NULL
AND columnname NOT IN ('NbrOfCounters')
GO
-- MERGE
RETURN
DECLARE @SQL VARCHAR(MAX) ;
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM (SELECT ';WITH s as (SELECT d.DataSupplierId, s.* FROM ' + FQN
+ ' AS s (NOLOCK) INNER JOIN Diablo.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.[CNTY_CD])
' + CASE WHEN ROW = 1
THEN 'INSERT [DiabloStg-DiabloLoad].[DataFolder-trans_pivoted] WITH (TABLOCK)
(DataSupplierId,
[T_BATCH_DT],
[T_BATCH_SEQ],
[Count1],
' + Util.dbo.StringConcat(ColumnName, ',
') + ')
SELECT
s.DataSupplierId,
s.[T_BATCH_DT],
s.[T_BATCH_SEQ],
s.[Count1],
' + Util.dbo.StringConcat('s.' + CASE WHEN Count1Max = 1 THEN 'Count1Max'
ELSE ColumnName
END, ',
')
+ '
FROM s
CREATE UNIQUE CLUSTERED INDEX trans_pivoted ON [DiabloStg-DiabloLoad].[DataFolder-trans_pivoted] (DataSupplierId, T_BATCH_DT, T_BATCH_SEQ, Count1) WITH (DATA_COMPRESSION = ROW)
' ELSE 'MERGE [DiabloStg-DiabloLoad].[DataFolder-trans_pivoted] WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.[T_BATCH_DT] = s.[T_BATCH_DT]
AND t.[T_BATCH_SEQ] = s.[T_BATCH_SEQ]
AND t.[Count1] = s.[Count1]
WHEN MATCHED THEN UPDATE SET
' + Util.dbo.StringConcat('t.' + ColumnName + ' = ' + CASE WHEN Count1Max = 1 THEN 's.Count1Max'
ELSE 's.' + ColumnName
END, ',
') + '
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
[T_BATCH_DT],
[T_BATCH_SEQ],
[Count1],
' + Util.dbo.StringConcat(ColumnName, ',
') + ')
VALUES
(s.DataSupplierId,
s.[T_BATCH_DT],
s.[T_BATCH_SEQ],
s.[Count1],
' + Util.dbo.StringConcat('s.' + CASE WHEN Count1Max = 1 THEN 'Count1Max'
ELSE ColumnName
END, ',
') + ');
'
END AS SQL
FROM (SELECT TOP 999999
*
FROM #Columns
ORDER BY ROW,
fqn,
columnname) c
GROUP BY ROW,
FQN) k
SET @SQL = REPLACE(REPLACE(REPLACE(REPLACE(@SQL, 'Diablo.', ''' + @DBNAME + ''.'), '[DiabloStg-DiabloLoad].[DataFolder-',
'DiabloStg.DiabloLoad.'' + @DataFolder + ''_'), ']', ''), '[', '')
EXEC Util.dbo.PrintLargeText
@SQL
GO
DECLARE @SQL VARCHAR(MAX) ;
WITH cols
AS (SELECT DISTINCT
ColumnName,
Count1Max,
ALIAS
FROM #Columns),
tabs1
AS (SELECT ROW,
FQN,
ALIAS
FROM (SELECT DISTINCT
ROW,
FQN,
ALIAS
FROM #Columns) K),
prikold
AS (SELECT 'COALESCE(' + Util.dbo.StringConcat(ALIAS + '.CNTY_CD', ',') + ') AS CNTY_CD,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.T_BATCH_DT', ',') + ') AS T_BATCH_DT,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.T_BATCH_SEQ', ',') + ') AS T_BATCH_SEQ,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.Count1', ',') + ') AS Count1,
' AS PriKeyList
FROM tabs1),
prikold2
AS (SELECT '
OUTER APPLY (SELECT TOP 1 CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1
FROM(VALUES' + Util.dbo.StringConcat('(' + ALIAS + '.CNTY_CD, ' + ALIAS + '.T_BATCH_DT, ' + ALIAS + '.T_BATCH_DT, ' + ALIAS + '.Count1)', ',
') + ')
cn (CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1)
WHERE CNTY_CD IS NOT NULL) cn' AS ca,
'DataSupplierId, cn.T_BATCH_DT, cn.T_BATCH_SEQ, cn.Count1,
' AS ColList
FROM tabs1),
prik
AS (SELECT '
CROSS APPLY(SELECT COALESCE(' + Util.dbo.StringConcat(ALIAS + '.CNTY_CD', ',') + ') AS CNTY_CD,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.T_BATCH_DT', ',') + ') AS T_BATCH_DT,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.T_BATCH_SEQ', ',') + ') AS T_BATCH_SEQ,
COALESCE(' + Util.dbo.StringConcat(ALIAS + '.Count1', ',') + ') AS Count1)cn
' AS ca,
'DataSupplierId, cn.T_BATCH_DT, cn.T_BATCH_SEQ, cn.Count1,
' AS ColList
FROM tabs1),
tabs
AS (SELECT TOP 999999
FQN,
ROW,
ALIAS,
ISNULL(JoinClause, 'FROM ' + FQN + ' ' + a.ALIAS + ' (NOLOCK)') AS JoinClause
FROM TABS1 a
OUTER APPLY (SELECT 'FULL OUTER JOIN ' + A.FQN + ' ' + a.ALIAS + ' (NOLOCK) ON ' + CASE WHEN MAX(b.ROW) = 1
THEN Util.dbo.StringConcat(b.ALIAS + '.CNTY_CD', '')
WHEN MAX(b.ROW) = 2
THEN 'ISNULL(' + Util.dbo.StringConcat(b.ALIAS
+ '.CNTY_CD',
', ') + ')'
ELSE 'COALESCE(' + Util.dbo.StringConcat(b.ALIAS
+ '.CNTY_CD',
', ') + ')'
END + ' = ' + a.ALIAS + '.CNTY_CD' + '
AND ' + CASE WHEN MAX(b.ROW) = 1 THEN Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_DT', '')
WHEN MAX(b.ROW) = 2 THEN 'ISNULL(' + Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_DT', ', ') + ')'
ELSE 'COALESCE(' + Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_DT', ', ') + ')'
END + ' = ' + a.ALIAS + '.T_BATCH_DT' + '
AND ' + CASE WHEN MAX(b.ROW) = 1 THEN Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_SEQ', '')
WHEN MAX(b.ROW) = 2 THEN 'ISNULL(' + Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_SEQ', ', ') + ')'
ELSE 'COALESCE(' + Util.dbo.StringConcat(b.ALIAS + '.T_BATCH_SEQ', ', ') + ')'
END + ' = ' + a.ALIAS + '.T_BATCH_SEQ' + '
AND ' + CASE WHEN MAX(b.ROW) = 1 THEN Util.dbo.StringConcat(b.ALIAS + '.Count1', '')
WHEN MAX(b.ROW) = 2 THEN 'ISNULL(' + Util.dbo.StringConcat(b.ALIAS + '.Count1', ', ') + ')'
ELSE 'COALESCE(' + Util.dbo.StringConcat(b.ALIAS + '.Count1', ', ') + ')'
END + ' = ' + a.ALIAS + '.Count1' AS JoinClause
FROM (SELECT TOP 999999
*
FROM TABS1 b
WHERE b.ROW < a.ROW
ORDER BY b.ROW) b) b
ORDER BY ROW),
tabsCases
AS (SELECT TOP 999999
FQN,
ROW,
ALIAS,
ISNULL(JoinClause, 'FROM ' + FQN + ' ' + a.ALIAS + ' (NOLOCK)') AS JoinClause
FROM TABS1 a
OUTER APPLY (SELECT 'FULL OUTER JOIN ' + A.FQN + ' ' + a.ALIAS + ' (NOLOCK) ON ' + CASE WHEN MAX(b.ROW) = 1
THEN Util.dbo.StringConcat(b.ALIAS + '.CNTY_CD = '
+ pAlias + '.CNTY_CD AND '
+ b.ALIAS + '.T_BATCH_DT = '
+ pAlias + '.T_BATCH_DT AND '
+ b.ALIAS + '.T_BATCH_SEQ = '
+ pAlias
+ '.T_BATCH_SEQ AND '
+ b.ALIAS + '.Count1 = '
+ pAlias + '.Count1', '')
ELSE 'CASE
' + Util.dbo.StringConcat('WHEN ' + b.ALIAS
+ '.CNTY_CD IS NOT NULL AND '
+ b.ALIAS + '.CNTY_CD = '
+ pAlias + '.CNTY_CD AND '
+ b.ALIAS + '.T_BATCH_DT = '
+ pAlias + '.T_BATCH_DT AND '
+ b.ALIAS + '.T_BATCH_SEQ = '
+ pAlias + '.T_BATCH_SEQ AND '
+ b.ALIAS + '.Count1 = '
+ pAlias + '.Count1 THEN 1', '
') + '
END = 1'
END AS JoinClause
FROM (SELECT TOP 999999
b.FQN,
b.ROW,
b.ALIAS,
p.ALIAS AS pAlias
FROM TABS1 b
INNER JOIN TABs1 p ON p.ROW = a.ROW
WHERE b.ROW < a.ROW
ORDER BY b.ROW) b) b
ORDER BY ROW),
ss
AS (SELECT FQN,
t.ALIAS,
Count1Max,
ColumnName,
t.ROW
FROM tabs t
CROSS JOIN cols),
tt
AS (SELECT TOP 999999
ss.fqn,
ss.ALIAS,
ss.ColumnName AS OrigColumn,
t.Count1Max,
t.ColumnName
FROM ss
LEFT OUTER JOIN #Columns t ON t.fqn = ss.fqn
AND ss.ColumnName = t.ColumnName
ORDER BY ss.ROW,
ss.ColumnName)
/* TRY 1
SELECT @SQL = (SELECT 'SELECT d.DataSupplierId, T_BATCH_DT, T_BATCH_SEQ, Count1,
' +
Util.dbo.StringConcat('MAX(' + ColumnName + ') as ' + ColumnName,',
') FROM cols ) + '
FROM ('
+
(SELECT Util.dbo.StringConcat(SQL, '
UNION ALL
') FROM(
SELECT 'SELECT CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1,
' + Util.dbo.StringConcat( CASE WHEN Count1Max = 1 THEN 'Count1Max' ELSE ISNULL(ColumnName, 'NULL') END + ' AS ' + OrigColumn, ',
') + '
FROM ' + FQN + ' (NOLOCK)' AS SQL
FROM tt
GROUP BY fqn)k) + '
)k
LEFT OUTER JOIN Diablo.tCommon.DataSupplier d(NOLOCK) ON d.CntyCd = k.CNTY_CD
GROUP BY d.DataSupplierId, T_BATCH_DT, T_BATCH_SEQ, Count1'
*/
--STEP 2
SELECT @SQL = (SELECT 'IF OBJECT_ID(''tempdb..#Keys'') IS NOT NULL DROP TABLE #Keys
CREATE TABLE #Keys(DataSupplierId SMALLINT NOT NULL, CNTY_CD CHAR(5) NOT NULL, T_BATCH_DT INT NOT NULL, T_BATCH_SEQ INT NOT NULL, Count1 TINYINT NOT NULL)
INSERT #Keys(DataSupplierId, CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1)
SELECT d.DataSupplierId, CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1
FROM (SELECT DISTINCT CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1
FROM (' + Util.dbo.StringConcat('SELECT CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1
FROM ' + FQN + ' (NOLOCK)', '
UNION ALL
') + ')k)k
LEFT OUTER JOIN Diablo.tCommon.DataSupplier d (NOLOCK) ON D.CntyCd = k.CNTY_CD
CREATE UNIQUE CLUSTERED INDEX KEYS ON #Keys (CNTY_CD, T_BATCH_DT, T_BATCH_SEQ, Count1)
'
FROM tabs) + '
' + 'SELECT k.DataSupplierId, k.T_BATCH_DT, k.T_BATCH_SEQ, k.Count1,
' + (SELECT Util.dbo.StringConcat(CASE WHEN Count1Max = 1 THEN ALIAS + '.Count1Max AS '
ELSE ALIAS + '.'
END + ColumnName, ',
')
FROM (SELECT TOP 999999
*
FROM tt
WHERE ColumnName IS NOT NULL
ORDER BY ColumnName) k) + '
FROM #Keys k (NOLOCK)
'
+ (SELECT Util.dbo.StringConcat('LEFT OUTER JOIN ' + fqn + ' ' + ALIAS + ' (NOLOCK) ON k.CNTY_CD = ' + ALIAS + '.CNTY_CD AND k.T_BATCH_DT = ' + ALIAS
+ '.T_BATCH_DT AND k.T_BATCH_SEQ = ' + ALIAS + '.T_BATCH_SEQ AND k.Count1 = ' + ALIAS + '.Count1', '
')
FROM tabs1)
/*-- FULL OUTER JOIN
SELECT @SQL = 'SELECT
' + (SELECT ColList FROM prik) + (SELECT Util.dbo.StringConcat(CASE WHEN Count1Max = 1 THEN Alias + '.Count1Max AS ' + ColumnName
ELSE ColumnName
END, ',
')
FROM cols) + '
' + (SELECT Util.dbo.StringConcat (joinclause, '
') FROM tabs) + (SELECT ca FROM prik) + '
LEFT OUTER JOIN Diablo.tCommon.DataSupplier D (NOLOCK) ON D.CntyCd = cn.CNTY_CD'
*/
EXEC Util.dbo.PrintLargeText
@SQL