问题描述
限时送ChatGPT账号..到目前为止我有这个代码:
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屋!
更多推荐
[db:关键词]
发布评论