在mssql服务器上,我尝试将单个随机记录从一个表传输到另一个表
我的sql脚本应该
从名为“test1”的表中选择一条随机记录
删除步骤1中的某个记录。从表“test1”
将此记录添加到表“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]
这个函数只传输整个表而不是一个随机行
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
带变量
任何帮助都将不胜感激
1条答案
按热度按时间sshcrbum1#