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.
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
What 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'