CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Amarundo
Amarundo

Reputation: 2397

How to SELECT and UNION from a group of Tables in the schema in SQL Server 2008 R2

I have a group of tables in my SQL Server 2008 R2 database, all of them with the same columns.

I need to SELECT and UNION these tables. I can SELECT the tables I want like this:

SELECT TABLE_NAME FROM information_schema.tables
where (TABLE_NAME like '%_CH1' or TABLE_NAME like '%_CH0')
and TABLE_NAME not like '%test%'

What I need now is to SELECT Column1, Column2, Column3 from the first guy in that list, UNION SELECT Column1, Column2, Column3 from the next guy in that list and so forth.

How do I do that?

Upvotes: 1

Views: 4805

Answers (2)

Taryn
Taryn

Reputation: 247670

@MarkD's answer will work great if the column names are identical, but if the column names are different, then you will need to query the INFORMATION_SCHEMA.COLUMNS to get the column names.

This is version that should work as well:

select row_number() over(order by (select 1)) id, table_name
into #tempTables
from information_schema.tables
where table_name like 'Table%'

declare @tempColumns table
(
    id int,
    column_name varchar(50),
    table_name varchar(50)
) 

declare @query varchar(max) = ''
declare @rownum int = 1
declare @maxrownum int = (select max(id) from #temptables)
declare @table varchar(50)

declare @colrow int = 1

while @rownum <= @maxrownum
  begin
    set @table = (select TABLE_NAME from #tempTables where id = @rownum)

    set @query = @query + ' SELECT '

    insert into @tempColumns    
    select row_number() over(order by (select 1)) id, COLUMN_NAME, @table
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = @table
        and ORDINAL_POSITION in (1, 2, 3)

        -- reset colrow to 1
        set @colrow = 1

        while @colrow <= 3
            begin

                set @query = @query + (select Column_name 
                                        from @tempColumns 
                                        where id = @colrow
                                            and table_name = @table) + ' as Col' + CAST(@colrow as varchar(50))

                if @colrow < 3
                    set @query = @query + ', '

                if @colrow <= 3
                    set @colrow = @colrow + 1

            end

    set @query = @query + ' FROM ' + @table

    if @rownum < @maxrownum
        set @query = @query + ' UNION ALL '

    if @rownum <= @maxrownum
        set @rownum = @rownum + 1
  end

exec(@query)

See SQL Fiddle with Demo

Upvotes: 3

MarkD
MarkD

Reputation: 5316

Dynamic query example;

--CREATE TABLE Utable1 (Col1 INT, Col2 INT, Col3 INT)
--CREATE TABLE Utable2 (Col1 INT, Col2 INT, Col3 INT)
--CREATE TABLE Utable3 (Col1 INT, Col2 INT, Col3 INT)


DECLARE  @SelectClause  VARCHAR(100)    = 'SELECT Col1, Col2, Col3'
        ,@Query         VARCHAR(1000)   = '' 

SELECT @Query = @Query + @SelectClause + ' FROM ' + TABLE_NAME + ' UNION ALL '
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE '%_CH1' OR TABLE_NAME LIKE '%_CH0')
AND TABLE_NAME NOT LIKE '%test%'

SELECT @Query = LEFT(@Query, LEN(@Query) - LEN(' UNION ALL '))

EXEC (@Query)

Upvotes: 5

Related Questions