Lesson 5: Dynamic SQL, OpenQuery and Cursors
https://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