步步为营：SQLServer查询随机不相同的记录插入临时表

DECLARE @Num VARCHAR(max)
DECLARE @SQL VARCHAR(max)
SET @Num = '1'
 
IF NOT EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##TABLE') and type='U')
BEGIN
    SET @SQL = 'SELECT top ' + @Num + ' id,Realname INTO ##TABLE FROM Edu_Users ORDER BY NEWID()'
    PRINT @SQL
    EXEC(@SQL)
    --SELECT top 1 id,Realname INTO ##TABLE FROM Edu_Users ORDER BY NEWID()
END
ELSE
BEGIN
    SET @SQL =    'INSERT INTO ##TABLE(id,Realname) SELECT TOP ' + @Num + ' id,Realname FROM Edu_Users WHERE NOT EXISTS(SELECT 1 FROM ##TABLE WHERE id=Edu_Users.id) ORDER BY NEWID()'
    PRINT @SQL
    EXEC(@SQL)
    --INSERT INTO ##TABLE(id,Realname) SELECT TOP 1 id,Realname FROM Edu_Users WHERE NOT EXISTS(SELECT 1 FROM ##TABLE WHERE id=Edu_Users.id) ORDER BY NEWID()
END

首先判断临时表是否存在.

不存在就执行SELECT INTO 随机记录

如果存在就执行INSERT INTO SELECT随机记录并且不重复