To set things up:
I have a server with ~23 databases. 14 use a standard naming convention aaa_001, aaa_002, aaa_003, etc...
Is there an EASY WAY to pull data from all 14 of these using one select statement (ie- SELECT * from aaa_*..table1)
I currently write a select statement for database aaa_001, UNION ALL and do the same select statement for aaa_002, etc. Looks like:
SELECT DISTINCT
table1.chickenname
FROM aaa_001..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_002..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_003..table1
I have even begun playing with DTS and spider webbing to each database. Select data from aaa_001.table1, dump to warehouse.table1, upon completion, elect data from aaa_002.table1, dump to warehouse.table1, upon completion, etc...
I have a server with ~23 databases. 14 use a standard naming convention aaa_001, aaa_002, aaa_003, etc...
Is there an EASY WAY to pull data from all 14 of these using one select statement (ie- SELECT * from aaa_*..table1)
I currently write a select statement for database aaa_001, UNION ALL and do the same select statement for aaa_002, etc. Looks like:
SELECT DISTINCT
table1.chickenname
FROM aaa_001..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_002..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_003..table1
I have even begun playing with DTS and spider webbing to each database. Select data from aaa_001.table1, dump to warehouse.table1, upon completion, elect data from aaa_002.table1, dump to warehouse.table1, upon completion, etc...