Lesson 5: Dynamic SQL, OpenQuery and Cursors

Facts and Dimensions provide 1000s of tables of publicly available stats and reference data, ready to query immediately.

In this video I show you how to use Dynamic SQL including with OpenQuery and in cursors.

TSQL from the video:

declare @sql varchar(max)
declare @Table_Schema varchar(max)
declare @Table_Name varchar(max)

create table #TempTable(Table_Schema varchar(255), Table_Name varchar(255), MaxEffDate date)

declare MyCursor cursor local for
select top 5 Table_Schema, Table_Name
from FD_UserDB.[Demo_Customer_UserDB].Release_Details.All_Available_Tables

open MyCursor
fetch next from MyCursor into @Table_Schema, @Table_Name

begin
print @Table_Schema + '.' + @Table_Name
set @sql = 'exec [Azure].[procCreateExternalTableScript_v4] ''' + @Table_Schema + ''',''' + @Table_Name + ''';'

set @sql = '
insert into #temptable
select *
from openquery(FD_UserDB,''select
''''' + @Table_Schema + ''''' as Table_Schema
,''''' + @Table_Name + ''''' as Table_Name
,max(Effective_Snapshot_Date) as MaxEffDate
from [' + @Table_Schema + '].[' + @Table_Name + '1]'')'

fetch next from MyCursor into @Table_Schema, @Table_Name
end

close MyCursor
deallocate MyCursor

select * from #temptable