Combining Data from Multiple tables

I have a requirment to take data from a large set of tables where the total number of these tables may change on a regular baisis and output into another table. All the tables willl have the same columns. Frequency is being debated but it maybe as much as once per hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of those tables to be inputted into another table. basically, I would want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records combined from all other _CPY tables

Ron SorrellWhat about this idea? This draft does not work properly because of filed name does not exists for all tables - but you modify for your case.

declare @.union varchar(8000)
set @.union='insert alltables'+char(13)
select @.union=@.union+' select name from '+name+char(13)+'union all'+char(13) from sysobjects where xtype='U'
select @.union=left(@.union,DATALENGTH(@.union)-10)
exec( @.union)