CODEWITHSUNDEEP
sql-server
Raccoon
Raccoon

Reputation: 1427

SQL Server : Dynamic Union Join

I have bunch of data tables named like the following:

As you can guess, the those numbers represent year and month. Now, what I need to do is to union-all-join only the necessary database tables using stored procedure depending on the start date and end date a user requests. ie) from 2014-01-01 to 2014-03-31

Well, I could come up with this shameful way to solve it...

// Create a varchar variable to keep appending queries
// While start date month < end_date
// {
//    Change start_date to yymm format
//    Query += SELECT * FROM db_yymm WHERE Blah Blah
//    Query += Union All
//    start date = start date + 1 month
// }
// Delete the unnecessary last Union All
// Fire the query somehow.

Is is technically possible and effective way to do it? Or is there a better way for it?

Upvotes: 2

Views: 7720

Answers (1)

Szymon
Szymon

Reputation: 43023

You are pretty much on track. This will look like this using dynamic SQL:

declare @startdate as date = '20140101' -- the start date should have day = 1
declare @enddate as date = '20140401' -- the end date should have day = 1

declare @date as date = @startdate

declare @sql as nvarchar(max) = N''

while @date <= @enddate
begin
    -- build the query for each month
    set @sql = @sql + N'SELECT * FROM db_table_' 
        + replace(convert(nvarchar(7), @date, 111), N'/', N'_')
        + N' UNION ALL '

    set @date = dateadd(month, 1, @date)
end

-- remove last N' UNION ALL '
if len(@sql) > 11
    set @sql = left(@sql, len(@sql) - 11)

-- execute
exec sp_executesql @sql

Upvotes: 8

Related Questions