Pete,

I posted the simple script in my other message. I suggest you to check this blog post for understanding of the idea

How to get information from all databases without a loop

>>You can get the list of tables with following query. Loop through it, build and execute INSERT INTO...SELECT FROM for each table.
>>

>>SELECT table_name
>>	FROM INFORMATION_SCHEMA.Tables
>>	WHERE TABLE_NAME LIKE 'IOM%'
>>

>>
>>>Hi All, I have a couple of hundred tables in SS that have a prefix of IOM in their name, they all have the same structure which is a single column. What I want to do is create a master table containing all the records of these tables - obviously I don't want to do this manually - any ideas ?
>
>Thanks Sergey, how do loop through the list of tables ?, I've tried selecting a table name into a variable - but how do I select from a table using my variable ?
>
>this is what I've tried
>
>

>
>declare @fname varchar(max)
>
>Select @fname = min(table_name)
>from information_schema.tables
>where table_type = 'base table'
>and left(table_name,3) = 'IOM'
>
>-- So far so good 
>
>While @fname != null
>begin
>
>-- How do I now select from @fname into a table ?
>
>Select @fname = min(table_name)
>from information_schema.tables
>where table_type = 'base table'
>and left(table_name,3) = 'IOM'
>and table_name > @fname
>
>end
>
>