CODEWITHSUNDEEP
sqlsql-servert-sql
MKP
MKP

Reputation: 117

Select from a dynamic table name in a view

My customer creates yearly a new table with the year included in the name that I have to use in a new view of my SQL Server database. I have solved this problem in a single query:

DECLARE @SQLString nvarchar(500)
SET @SQLString = 'SELECT * FROM [MYDATABASE].[dbo].[MYTABLE_'+cast(YEAR(GETDATE()) as varchar(4))+']'
EXECUTE sp_executesql @SQLString

but I cannot use an execute statement in a view. I've also tryied to move it to a function but the problem is the same. ¿What could I do?

Thanks in advance.

Upvotes: 2

Views: 5428

Answers (2)

HABO
HABO

Reputation: 15852

Since your view only references the "latest" table you can refer to that table using a synonym:

create synonym dbo.CurrentYearVendorTable for dbo.VendorStuffFor2019;

create view dbo.MyView as
  select *
    from dbo.CurrentYearVendorTable; -- Note: Reference to synonym, not table.

When the new table is created replace the synonym and update the view:

drop synonym dbo.CurrentYearVendorTable;
create synonym dbo.CurrentYearVendorTable for dbo.VendorStuffFor2020;
execute sp_refreshview @viewname = 'dbo.MyView';

For other uses it may simply suffice the use the synonym in queries.

Upvotes: 0

ser_nicky
ser_nicky

Reputation: 356

I don't know if it is an option for you, but you can dynamically create a view itself. Something like this:

declare @sql varchar(1000)
,@sql2 varchar(1000)

set @sql = ('create view x as select * from MyTable_' + convert(varchar(10),year(getdate())) + ' go')

set @sql2 = 'select * from x'

exec (@sql)
exec (@sql2)

Upvotes: 1

Related Questions