none
T-SQL to connect to latest table

    Question

  • Hi Folks

    I am new to T-SQL, so was looking for some help with a script. 

    I have some monthly tables. There names consist of a static and "dynamic" component (i.e "sales_" being static, and the YYYYMM part  changing each month. For example 

    1. Sales_201212
    2. Sales_201301
    3. Sales_201302

    I need a T-SQL script to connect to the previous months table.  So if the code was run on the 15th of  Feb 2013, I want to return all sales from the "Sales_201301" table. Similarly if I can the code back on the 7th of Jan 2013, I would want to return all values from the "Sales_201212"  table.

    My assumption has been the script would use a combination of a datepart  and getdata () expression such as 

    select *
    FROM 'Sales_' + convert(nvarchar(4), datepart(yyyy, getdate()) + convert(nvarchar(2), datepart(mm, getdate())))

    Can someone help me out here?

    Regards

    Steve

     


    • Edited by Fox_00 Thursday, February 14, 2013 7:36 PM
    Thursday, February 14, 2013 7:34 PM

Answers

  • I agree with Serg  but if this is something out of your hands try below. if the tables are big, think about partition and test if that helps.

    declare @sql nvarchar(max)
    set @sql = 'Select * from test' + CONVERT(VARCHAR(6), DateAdd(month,-1,getdate()), 112)
    select @Sql
    Execute sp_executesql @sql

    Hope it Helps!!

    • Marked as answer by Fox_00 Thursday, February 14, 2013 8:10 PM
    Thursday, February 14, 2013 8:08 PM

All replies

  • With a design like that you need  dynamic Sql http://msdn.microsoft.com/en-us/library/ms188001.aspx

    I strongly advice that you redesign your tables.


    Serg

    • Proposed as answer by Naomi NModerator Thursday, February 14, 2013 7:48 PM
    • Unproposed as answer by Fox_00 Thursday, February 14, 2013 8:00 PM
    Thursday, February 14, 2013 7:40 PM
  • I agree with Serg  but if this is something out of your hands try below. if the tables are big, think about partition and test if that helps.

    declare @sql nvarchar(max)
    set @sql = 'Select * from test' + CONVERT(VARCHAR(6), DateAdd(month,-1,getdate()), 112)
    select @Sql
    Execute sp_executesql @sql

    Hope it Helps!!

    • Marked as answer by Fox_00 Thursday, February 14, 2013 8:10 PM
    Thursday, February 14, 2013 8:08 PM
  • As other have already mentioned, it is a very bad idea to get into dynamic SQL to achieve this.

    Why do you want to use separate tables for separate months? With proper indexing, there is no problem with using just one table (with a fixed name).

    If you want to stick with your design, then you could create a (partitioned) view, and use the view to create one virtual table, and use it in all your queries. Then, when a new month table needs to be added (or deleted) you alter the view.

    CREATE VIEW Sales AS
      SELECT my_col1, my_col2 FROM dbo.Sales_201212
      UNION ALL
      SELECT my_col1, my_col2 FROM dbo.Sales_201301
      UNION ALL
      SELECT my_col1, my_col2 FROM dbo.Sales_201302
    
    SELECT *
    FROM Sales
    


    Gert-Jan

    Thursday, February 14, 2013 8:13 PM
  •  Thanks for everyone's input.

    A couple of additional requests. Can someone suggest some good overview articles on

    1.  The pros and cons of dynamic SQL
    2. Introduction to partitions 101.

    Many thanks. 

    Steve

    Thursday, February 14, 2013 8:15 PM
  • for dynamic sql this is one of the best http://www.sommarskog.se/dynamic_sql.html

    For basics on partition, look for some free videos on youtube.


    Hope it Helps!!


    • Proposed as answer by Naomi NModerator Thursday, February 14, 2013 8:20 PM
    • Edited by Stan210 Thursday, February 14, 2013 8:21 PM
    Thursday, February 14, 2013 8:19 PM