Is it possible to update a variable for each row in select-CodePudding

In MySQL it can be easily done like

set @var= (select value from my_sequence);

insert into new_table (id, name)
select @var := @var   1, name from old_table;

But I cannot find a way to do the same thing in SQL Server. Is it possible? What are the alternatives?

CodePudding user response：

Just use 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;

You could even achieve this without a variable:

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);

As a side note, syntax SELECT @Variable = @Variable ... FROM is a documented antipattern in SQL Server and should be avoided.