We help IT Professionals succeed at work.

Automating Query

I am currently doing the below manually at the moment and wandered if it could be automated

I have a table called metrics, it lists all the metrics that run overnight against an accounting application (SQL 2008 R2)

The table holds the metric name and id, examples of which are

ID                 Metric
1                   Billing2345
2                   Ledger2011
3                   Billing2547

I am currently using the last 12 billing metrics and putting them in a query i.e. any metric beginning with Billing, the metric has a number at the end, in the above examples 2345,2547. The numbers do not go up by one exh time i.e. 2345,2346 etc. All I know is the lastest running 2547 will be higher than last running

There are tables with the same name as the Metric name, i.e. based on the example above there is a table called Billing2345 and Billing2547.

At the moment I am looking at the metrics table and getting the last 12 tables based on the number at the end of the table name and putting them in a query as below

Select etc.
from Billing2345
where etc.

union all

Select etc.
from Billing2547
where etc.

When there is a latest version of the metric I deleted the first part of the query and add the latest table on at the end

This is all a pain.

Is there a way of automating this

Thanks
Comment
Watch Question

Commented:
You can use dynamic SQL query to do it automatically.

Examples:

http://www.techrepublic.com/blog/datacenter/generate-dynamic-sql-statements-in-sql-server/306
SQL Expert/Infrastructure Architect
Commented:
I agree with the dynamic sql, I did a small script for you to start with,

--------------------------- Script Start ------------------------------------
-- You need a character variable to load with dynamic sql
Declare @sql varchar(max)

-- To use @sql=@sql etc etc, it cannot be null, therefore I set it to ''
set @sql=''

-- Now populate your SQL sentence from the metrics table, and metric column. Only the tweleve last BillingXXXX names
select top 12 @sql=@sql + CHAR(13) + 'Select etc1 from ' + metric + ' Where etc2' + CHAR(13) + 'Union all'
from metrics
where metric like 'Billing%'
order by id desc

-- Remove the last union all (plus one sign for the char(13) character)
select @sql = LEFT(@sql,(len(@sql)-len(' Union all')))

-- View the sqlcode
print @sql

-- To automatically run it, uncomment the exec(@sql) part
--exec(@sql)
---------------------------- Script End ------------------------------------

Use the script in the database holding the metrics table, or add a qualifyer, i e
change  "from metrics"
To "from Database.Schema.metrics"

Sincerely Marten
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>it lists all the metrics that run overnight against an accounting application (SQL 2008 R2)
Is there any chance that whatever process creates these new tables be changed so that it always appends to a single table, with a datestamp so you can tell the runs apart?
That way a single view will always work.
Marten RuneSQL Expert/Infrastructure Architect

Commented:
To add to Jimhorns idea, good one bye the way!

If you cannot change this process (chances are that you cant), you could create another database, and populate this table dynamically (using a modified version of the script aboce), but first you would truncate it ofcourse.

Then you would have one table to query, and the select etc1, and where etc2 part is easily changed to whatever is convienient. You could even query this table (its a static name) to a Excelfile or whatever in the same batch.

All this I would run in a job at a time before I come to the office (given the metrics batch has ended some time before this).

I trust you let me elaborate on your idea, Jimhorn

Sincerely Marten