>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