Thursday, February 14, 2013 7:34 PM
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
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?
- Edited by Fox_00 Thursday, February 14, 2013 7:36 PM
Thursday, February 14, 2013 7:40 PM
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.
Thursday, February 14, 2013 8:08 PMI 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 @SqlExecute sp_executesql @sql
Hope it Helps!!
- Marked As Answer by Fox_00 Thursday, February 14, 2013 8:10 PM
Thursday, February 14, 2013 8:13 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
Thursday, February 14, 2013 8:15 PM
Thanks for everyone's input.
A couple of additional requests. Can someone suggest some good overview articles on
- The pros and cons of dynamic SQL
- Introduction to partitions 101.
Thursday, February 14, 2013 8:19 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!!