Creating a temporary table name with a randomly generated number

Question

So far I have this code:

declare @random int, @upper int, @lower int, @rndtb varchar(20)
set @lower = 1
set @upper = 999
select @random = ROUND(((@upper - @lower) * rand() + @lower),0)
select @rndtb = '##show'+cast(@random as varchar(20))+''

But that gives me

Conversion failed when converting the varchar value '##show' to data type int.

What I am trying to achieve is to create a table ##show+random number each time the query is executed.

Example :

##show01
##show78
##show43

Edited with what @bluefeet said and found a way to create the table with

Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @rndtb + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

but how do I call or insert into this table?

Answer 1

Since you are adding the parameter @random to a string, you need to cast it as a varchar so it can be concatenated to the string portion:

select @rndtb = '##show'+cast(@random as varchar(20))+''

Your full code will be:

declare @random int, @upper int, @lower int, @rndtb varchar(20)
set @lower = 1
set @upper = 999
select @random = ROUND(((@upper - @lower) * rand() + @lower),0)
select @rndtb = '##show'+cast(@random as varchar(20))+''

Based on your edit, you will need to use dynamic sql to insert into the new table:

select @SQL = 'insert into '+@rndtb+'
        select *
        from yourtable'


exec (@sql)