Lesson 5: Dynamic SQL, OpenQuery and Cursors

factsanddimensions.co.uk/
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

while @@FETCH_STATUS = 0
begin
print @Table_Schema + '.' + @Table_Name
set @sql = 'exec [Azure].[procCreateExternalTableScript_v4] ''' + @Table_Schema + ''',''' + @Table_Name + ''';'
exec(@sql) at FD_UserDB

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]'')'
exec(@sql)

fetch next from MyCursor into @Table_Schema, @Table_Name
end

close MyCursor
deallocate MyCursor

select * from #temptable