Per row sql generation – union, #table, into

Základní syntaxe pro rychlé generování pro kařdý řádek tabulky. S vyřešeným unionem, into clause a práce s #tabulkou.
Agregace jsou jalové.

DECLARE @tmp_table_name nvarchar(255) = 'sql_list'

SELECT 'IF OBJECT_ID(''tempdb..#'+@tmp_table_name+''') IS NOT NULL
  DROP TABLE #sql_list;' AS sql --temp table reset
UNION ALL

SELECT --one row per table
'SELECT 
    '''+ tab.table_name+''' AS table_name
    ,MIN(valid_date) as first_day
    ,MAX(valid_date) as last_day
    ,COUNT(distinct valid_date) as days_amnt
    ,COUNT(*) as rows_total
    '+CASE WHEN row_number() OVER (ORDER BY tab.table_name) = 1 THEN 'INTO #'+@tmp_table_name+ Char(10) ELSE '' END   + --into clause first row only
    'FROM ['+tab.table_catalog+'].['+tab.table_schema+'.['+ tab.table_name+ ']
    WHERE valid_date > ''2014-12-31''
    '+CASE WHEN row_number() OVER (ORDER BY tab.table_name) = sum(1) OVER () THEN '' ELSE Char(10)+'UNION ALL ' END AS sql  --union all on all rows except last one
FROM information_Schema.tables tab
WHERE 1=1
AND tab.table_name LIKE 'stage_%' --<<<<filter table name
AND tab.table_catalog LIKE 'STAGE' --<<<<filter db name
AND tab.table_schema LIKE 'primary1' --<<<<filter schema name

UNION ALL
SELECT 'select * from #'+@tmp_table_name+'
order by pocet_dni desc'











Leave a Comment

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *