Dynamic sql to join a table


  • Good Evening,

    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
          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 

    Thank you in advance

    Friday, July 19, 2013 8:13 PM


All replies