sql - 将 1 个随机记录从一张表转移到另一张表

Question

在 mssql 服务器上，我尝试将单个随机记录从一张表转移到另一张表
我的 sql 脚本应该

从名为“Test1”的表中选择一条随机记录

从表 'Test1'

中删除在步骤 1 中获得的这条特定记录。

将此记录添加到表“Test2”

到目前为止我发现
从表 Test1 中获取一条随机记录

SELECT TOP 1 
    *
FROM 
    [Test1]
ORDER BY
    NEWID()

删除名为“uno”的列等于“1”的记录，并将删除的记录放入名为“Test2”的表中

DELETE FROM
   [Test1]
OUTPUT
   deleted.*
FROM
   [Test2]
WHERE
   [uno] = '1'

我试过这个失败了

DELETE I FROM (
    SELECT 
        *
    FROM
        [Test1]
    ORDER BY
        NEWID()
) I
OUTPUT
    deleted.*
INTO
    [Test2]

这有效但传输整个表而不是仅 1 个随机行

DELETE FROM
    [Test1]
OUTPUT
    deleted.*
INTO
    [Test2]
WHERE EXISTS (
        SELECT TOP 1 
            *
        FROM 
            [Test1]
        ORDER BY
            NEWID()
    )

我也尝试分别添加和删除
通过在变量中捕获函数 NEWID() 的结果
然后以这种方式插入和删除具有相同唯一 id 的行

DECLARE @NewReportID uniqueidentifier 
SET @NewReportID = NEWID()

INSERT INTO
   [Test2]
SELECT TOP 1
   *
FROM
   [Test1]
ORDER BY
   @NewReportID
 
GO

DELETE TOP 1 FROM
   [Test1]
ORDER BY
   @NewReportID

但它失败了，因为没有办法使用 ORDER BY带变量
任何帮助将不胜感激

Answer 1

WITH CTE AS (
SELECT TOP 1 
    *
FROM 
    [Test1]
ORDER BY
    NEWID()
)
DELETE FROM CTE 
OUTPUT deleted.*
INTO [Test2]