Create global temporary tables, which are in the same scope.
Name the tables with numbers, e.g. ##1.
Before you create the temporary table, get the next available number by running a loop to see which one doesn't exist.
DECLARE @tblNum INT,@TblName varchar(25)
SET @tblnum = 1
WHILE @tblNum < 1000 --some large number to try the loop up to
SET @tblName = 'tempdb..##' + ltrim(str(@tblnum))
IF OBJECT_ID(@name) IS NULL --this table name isn't used yet
SET @tblNum = @tblNum + 1
--Here have the code for:
1. If @tblNum = 1000, we broke b/c we exceeded the highest number
2. create the temp table with the latest value in @tblName
Sarah Berger MCSD