This:

declare @StartYear int = 2007,

@SQLCmd nvarchar(max);

with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))

select

@SQLCmd = stuff((select char(13) + char(10) +

'select * from Table_' +

cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +

' where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'') union all'

from

quickTally

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +

char(13) + char(10) + 'select * from TableCurrent where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'');'

;

print @SQLCmd;