Posted by laurenq uantrell on 12/17/05 17:36

Mike,
Thanks for that. Unfortunately, the client architecture rules require
the data to reside in approx 250 different tables.

So, what I'm considering, before I shoot myself, is playing with
something like this (though I suspect the performance will totally blow
with each table holding average of 50K records.):

(foo code)

DECLARE @counter smallint, @tablename varchar(20), @sql nvarchar(4000)
SELECT @tablename = 'tblBaseName'

SELECT @sql = 'SELECT * FROM tblBaseName1'

SET @counter = 2
WHILE @counter < 251 /*max table count*/
BEGIN
SET NOCOUNT ON
@tablename = @tablename + CAST(@counter as varchar(3))
SELECT @sql =

@sql + ' UNION ALL SELECT * FROM ' + @tablename

SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO

EXEC sp_executesql @sql

[Back to original message]