使用随机生成的数字创建临时表名 [英] Creating a temporary table name with a randomly generated number

问题描述

到目前为止我有这个代码:

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))+''

但这给了我

将 varchar 值##show"转换为数据类型 int 时转换失败.

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

我想要实现的是每次执行查询时创建一个表##show+random number.

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

示例:

##show01
##show78
##show43

使用@bluefeet 所说的进行编辑，并找到了一种使用

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?

推荐答案

由于将参数 @random 添加到字符串中，因此需要将其强制转换为 varchar 以便可以将其连接到字符串部分:

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))+''

您的完整代码将是:

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))+''

根据您的编辑，您将需要使用动态 sql 插入新表:

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)

这篇关于使用随机生成的数字创建临时表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！