0

I have an MSSQL (2016) DB with multiple tables for accounts of customer that have similar names like bandwidth_timeseries_ACC_1111111, bandwidth_timeseries_ACC_222222 etc. They are identical to each other in design and data types. In addition I have a control table with the following structure:

AccountID      Report_Name   Appear_In_View
111111       QUARTERLY REPORT   1
222222       QUARTERLY REPORT   0
[...]

And in the loop should be included those tables belonging to those accounts where the bit "Appear_In_View" =1 only. Also in each physical table (like bandwidth_timeseries_ACC_1111111) there is a column named "Most_Recent_Data" which is also a bit (0 or 1) and only lines where it's set to 1 should be included. All the lines from each table should be inserted into a temp table that unions the physical table.

Up until today I had an SP which performed a similar thing only by creating Views that UNION all tables that match the above criteria, however I was advised of a possible performance problems down the road because the union query is growing rapidly in length and that using small INSERT INTO queries in a loop are a better solution.

I know the implementation should be done using T-SQL but I'm not very proficient in writing in this language and would appreciate any assistance that can be offered. Thanks.

2

If you want to avoid a union, you can loop using a cursor.

So, first you define your cursor that will list all the tables you want to query (let's assume you have all your data tables listed somewhere (system tables or config)).

And then loop doing :

  • create a dynamic SQL statement with the name of the table you retrieved
  • execute this statement

So something like:

declare @sql varchar(max)
declare @data_table varchar(50)
declare data  cursor for 
    select data_table   -- name of the table you have to query
    from somewhere      -- where these table names are stored

open data
fetch next from data into @data_table

while (@@fetch_status = 0) 
begin
    -- result_table = name of the table you want the results to be stored
    -- control_table = name of the control table you mentioned
    set @sql = 'insert into result_table
                select *
                from '+@data_table+'
                where AccountId in (select AccountId
                                    from control_table
                                    where Appear_In_View = 1)
                and Most_Recent_Data = 1'
    exec(@sql)
    fetch next from data into @data_table
end

close data 
deallocate data 

EDIT : with new inputs from Ariel Hayoun Try this kind of where clause if (and only if) there is one line te retrieve:

set @sql = 'insert into result_table
            select *
            from '+@data_table+'
            where AccountId in (select AccountId
                                from control_table
                                where Appear_In_View = 1)
            and Retrieved_At = (select max(Retrieved At) from '+@data_table +')'

If you have multiple lines per tables (one line per account id for instance), you may be able to achieve what you want using row_number() function

  • Thanks inmias, I've adapted your code with my table names and it seems to do the job. I do have a DB that's a little different than what I've described. In that DB the tables don't have a column of "Most_Recent_Data" but rather a "Retrieved At" column of DATETIME type (like 2016-12-14 12:54:00). In this case I would need to select only the rows that have the most recent time (I though about using something like max([retrieved_at])) but it wont be accepted as part of the WHERE clause so I need to achieve it some other way. – Ariel Hayoun Jan 5 '17 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.