Table concatenation

Question

I want to have a dynamic query that I can concatenate my table based on current month. I want to have a variable to my FROM and JOIN statements.

DECLARE @month_number AS VARCHAR(2);
DECLARE @table_name AS VARCHAR(15);
DECLARE @table AS VARCHAR(MAX);
SET @month_number = (SELECT month(getdate()));
SET @table_name = 'Sessions_month_'
SET @table = @table_name+@month_number;

SELECT * FROM Sessions_month_+@month_number where id = (SELECT id FROM Sessions_month_+@month_number WHERE Contact = 2);

Or should I do this:

https://stackoverflow.com/questions/19939380/how-to-form-a-table-name-from-concatenating-strings-in-select-statement-in-sql-2

Answer 1

I am not here to judge whether it is a good idea to use dynamic sql, but just for your question, you may use the following code to reach your goal

DECLARE @month_number AS VARCHAR(2);
DECLARE @table_name AS VARCHAR(15);
DECLARE @table AS VARCHAR(MAX);
declare @qry nvarchar(max);
SET @month_number =  month(getdate());
SET @table_name = 'Sessions_month_'
SET @table = @table_name+@month_number;

set @qry='SELECT * FROM Sessions_month_' + @month_number +' where id = (SELECT id FROM Sessions_month_'+@month_number +' WHERE Contact = 2);'
exec sp_executesql @qry;
go

Answer 2

This is a bad idea. You will need to use Dynamic SQL to do this. Be aware of the performance and security implications of it. Be sure to read through the Dynamic SQL Bible to understand the pros and cons of it: http://www.sommarskog.se/dynamic_sql.html.

Answer 3

How about you use a view, and change the definition of the view as the months change, on a scheduled job? You would just do all queries against this view, to make sure you're always getting the latest data.

Example:

CREATE VIEW 
           dbo.Sessions_month 
       AS 
           (SELECT Columns FROM Sessions_month_1) 
       GO

Then your job would do something like this:

USE your-database
GO
DECLARE @SQL NVARCHAR(max)
SET @SQL = 'ALTER VIEW dbo.Sessions_month AS 
            (SELECT Columns FROM Sessions_month_' 
           + month(getdate()) + ')'

EXEC (@SQL)