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)

Column Totalling Question

I am trying to total a column and I am not sure how to do this.
I have an IIF statment in a textbox in my table in which subtracts 2
columns, sees if the calculated value is less than zero, and if the value is
less than zero display 0 else display the value. See Below
=IIF((MSRptISCClass.ISC.intGetSumORHours(Fields!EmployeeNumber.Value))-(MSRptISCClass.ISC.intGetORHoursLogged(Fields!EmployeeNumber.Value))
<
0,0,(MSRptISCClass.ISC.intGetSumORHours(Fields!EmployeeNumber.Value))-(MSRptISCClass.ISC.intGetORHoursLogged(Fields!EmployeeNumber.Value)))
What I want to do is take the sum of all these results in the footer of the
table but I cant wrap a Sum aggregate function around the above statement and
I cant use the running value function because it doesn't take in account the
negative values that I handle in the text box. ANY SUGGESTIONS!!!
DONI had a situation like this. I ended up creating total rows in a stored
procedure along with the other detail rows. To distinguish a row, I added a
rowtype column. I used the CASE statement within the stored procedure to
calc totals. Hope this helps!
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:B5BE15B7-817A-4755-8422-26D9A4C28896@.microsoft.com...
>I am trying to total a column and I am not sure how to do this.
> I have an IIF statment in a textbox in my table in which subtracts 2
> columns, sees if the calculated value is less than zero, and if the value
> is
> less than zero display 0 else display the value. See Below
> =IIF((MSRptISCClass.ISC.intGetSumORHours(Fields!EmployeeNumber.Value))-(MSRptISCClass.ISC.intGetORHoursLogged(Fields!EmployeeNumber.Value))
> <
> 0,0,(MSRptISCClass.ISC.intGetSumORHours(Fields!EmployeeNumber.Value))-(MSRptISCClass.ISC.intGetORHoursLogged(Fields!EmployeeNumber.Value)))
> What I want to do is take the sum of all these results in the footer of
> the
> table but I cant wrap a Sum aggregate function around the above statement
> and
> I cant use the running value function because it doesn't take in account
> the
> negative values that I handle in the text box. ANY SUGGESTIONS!!!
> DON