none
Dynamic sql to join a table

    Question

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

    Friday, July 19, 2013 8:13 PM

Answers

All replies