在 MySQL 中，它可以很容易地完成
set @var= (select value from my_sequence);
insert into new_table (id, name)
select @var := @var 1, name from old_table;
但我找不到在 SQL Server 中做同樣事情的方法。是否可以？有哪些替代方案？
uj5u.com熱心網友回復：
只需使用ROW_NUMBER：
DECLARE @Var int = (SELECT [value] FROM dbo.my_sequence WITH (UPDLOCK));
--Ensure the row can't be used while we're doing it, as I assume that
--the value would be updated later with the new sequence value
INSERT INTO dbo.new_table (id, name)
SELECT ROW_NUMBER() OVER (ORDER BY {Column to Order By}) @Var,
[name]
FROM dbo.old_table;
你甚至可以在沒有變數的情況下實作這一點：
INSERT INTO dbo.new_table (id, name)
SELECT ROW_NUMBER() OVER (ORDER BY {Column to Order By}) ms.[value],
ot.[name]
FROM dbo.old_table ot
CROSS JOIN dbo.my_sequence ms WITH (UPDLOCK);
附帶說明一下，語法SELECT @Variable = @Variable ... FROM是SQL Server 中記錄的反模式，應該避免使用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/466595.html
上一篇：從SQLServer中的nvarchar資料型別列中選擇xml標記
下一篇：每月SQL活躍用戶數