I am stuck on writing some code, I need to join on a table but the table name changes every month (To the latest data), I need to be able to create a join that always joins to the latest table. This is as far as I have got, but there is an error message which says:-
Database 'SELECT * FROM ORD_LossProv' does not exist. Make sure that the name is entered correctly.
The code I have created is as follows:-
DECLARE @TableName AS varchar(50) DECLARE @SQL AS varchar(max) WITH Latest AS( SELECT create_date = MAX(create_date) FROM sys.tables WHERE name LIKE '%HPI_TABLE%' ) SELECT @TableName = name FROM Latest JOIN sys.tables AS HPI ON HPI.create_date = Latest.create_date SET @SQL = 'SELECT * FROM ORD_LossProv.LossProv.'+@TableName EXEC @SQL
Thank you in advance