从列表中插入一个随机字符串到表中

Question

我试图插入一个随机部门名称到一个SQL Server表。目前我有下面的代码。我希望在用样本数据填充表时随机插入所列出的四个部门值(comsci、生物学、心理学、化学)中的任何一个。如有任何帮助，不胜感激

Declare @SID int
Set @SID = 1
/* Create temporary table to insert random department name 
declare @myList table (Dept varchar(50))
insert into @myList values ('Computer Science'), ('Biology'), ('Psychology'), ('Chemistry')*/
While @SID <= 12000
Begin 
Insert Into Student values ('Student', CAST(@SID as nvarchar(10)), 'Department' + CAST(@SID as nvarchar(10)), '50')
Print @SID
Set @SID = @SID + 1
End

Answer 1

使用select(而不是values)从您创建的列表中选择并插入按newid()排序的top 1。

--insert into dbo.Student (Name, id, Department, Position)
select top 1 'Student', CAST(@SID as nvarchar(10)), Dept, '50'
from @myList
order by newid();

注意:最佳实践是列出插入到。

中的所有列。

Answer 2

对于TSQL，您可以生成一个随机数，然后使用第一个数字来确定使用哪个值，例如

declare @ran int = (select rand() * 10)
declare @subject varchar(100)=''
if @ran <= 3
set @subject = 'computer science'
else if @ran <= 6
set @subject = 'Biology'
else if @ran <= 8
set @subject = 'Psychology'
else
set @subject = 'Chemistry'
insert into table ...... etc etc

Answer 3

因为您需要12,000个随机数据，所以使用@myList到CROSS JOIN本身。并使用row_number()生成序号。random部分由NEWID()处理

with stud as
(
select  SID = row_number() over (order by newid()),
l1.Dept
from    @myList l1
cross join @myList l2
cross join @myList l3
cross join @myList l4
cross join @myList l5
cross join @myList l6
cross join @myList l7
)
select  *
from    stud
where   SID <= 12000