Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: 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.

    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 Sorrell

  2. #2
    Join Date
    Jul 2002
    Village, MD

    Re: Combining Data from Multiple 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'
    select @union=left(@union,DATALENGTH(@union)-10)
    exec( @union)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts