Concat multiple database tables

[gbrian]

I have 25 databases that all contain the same "Table X"

What I would like to do is create some sort of view that shows the data from all of these tables in one listing.

What are my options for doing this directly in MS SQL Server?

 

[SQLDenis]

create view TheMotherOffAllTableX
as
select * from db1.dbo.TableX
union all
select * from db2.dbo.TableX
union all
select * from db3.dbo.TableX
union all
select * from db4.dbo.TableX
union all
select * from db5.dbo.TableX
union all
select * from db6.dbo.TableX
...
...
...
union all
select * from db25.dbo.TableX

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

try this:

declare @test varchar(8000)
declare @Db varchar(8000)
declare @flFirst bit
DECLARE Test CURSOR READ_ONLY FAST_FORWARD FOR
        SELECT Catalog_name from INFORMATION_SCHEMA.SCHEMATA
SET @Test    = ''
SET @flFirst = 1
OPEN Test
FETCH NEXT FROM Test INTO @db
WHILE @@FETCH_STATUS = 0
      BEGIN
          IF OBJECT_ID(@db+'.dbo.TableX') IS NOT NULL
             BEGIN
                IF @flFirst = 0
                   SET @Test = @Test + ' UNION ALL '

                   SET @Test    = @Test + ' SELECT * FROM '+@db+'.dbo.TableX'
                   SET @flFirst = 0
              END
         FETCH NEXT FROM Test INTO @db
      END

PRINT @test
CLOSE Test
DEALLOCATE Test

EXEC(@test)

If you don't want to use CURSOR and dynmaic SQL, you could use something like:

SELECT * FROM DB1.dbo.TableX
UNION ALL
SELECT * FROM DB2.dbo.TableX
UNION ALL
SELECT * FROM DB3.dbo.TableX
....
UNION ALL
SELECT * FROM DB25.dbo.TableX

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Denis, I love the name of the view TheMotherOffAllTableX

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gbrian]

LOL, I love the name too--I think I'll use it!

Thanks everyone. I'll let you know what worked best.