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

 

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

 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

Answer 5

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