Apr 242012

How do you query BAMArchive data? The tables that are created are time stamped.
A friend asked me about this, so I thought I would put a little example out there for everyone to see.
With the BAMarchive, all of the columns are the same (for a particular activity), so all I really need to do is UNION ALL the data.
(Oh this query has to be run against the database that the tables live)

use BAMArchive GO --Create all of the tables create table Table_1 (data nvarchar(100)) GO create table Table_2 (data nvarchar(100)) GO create table Table_3 (data nvarchar(100)) GO create table Table_4 (data nvarchar(100)) GO --Insert some data into each table insert into Table_1 select 'data for table 1' insert into Table_2 select 'data for table 2' insert into Table_3 select 'data for table 3' insert into Table_4 select 'data fro table 4' --Here is the actual work declare @statement nvarchar(max) set @statement='' select @statement=@statement+'select data from '+ name +' union all ' from sys.tables --Only get the tables that were created within the past day --but here you would want to include the activity name where create_date > dateadd(d,-1,GETDATE()) --Remove the last union all set @statement=substring(@statement,0,len(@statement)-9) --Show the query we are about to run select @statement [Query] --Actually run the statement exec (@statement) --dropping tables so you can run the query again drop table Table_1 GO drop table Table_2 GO drop table Table_3 GO drop table Table_4 GO

Sorry, the comment form is closed at this time.