Question of the Day

Inserting Sequences

I created a new sequence in SQL Server 2022 with this code.

CREATE SEQUENCE myseqtest START WITH 1 INCREMENT BY 1;
GO

I want to use this to insert some data from another table into a new table with this sequence. Which of these queries shows the way to do this efficiently?

-- 1
INSERT dbo.NewMonthSales
  (SaleID, saleyear, salemonth, currSales)
SELECT
  NEXT VALUE FOR myseqtest
, ms.saleyear
, ms.salemonth
, ms.currMonthSales
FROM dbo.MonthSales AS ms;
GO

-- 2
INSERT dbo.NewMonthSales
  (SaleID, saleyear, salemonth, currSales)
SELECT
  NEXT VALUE 
, ms.saleyear
, ms.salemonth
, ms.currMonthSales
FROM dbo.MonthSales AS ms, myseqtest;
GO

--3 
DECLARE mycurs CURSOR FOR SELECT
 ms.saleyear
, ms.salemonth
, ms.currMonthSales
FROM dbo.MonthSales AS ms

DECLARE @yr INT, @mn INT, @sales NUMERIC(10,2)

FETCH NEXT FROM mycurs INTO @yr, @mn, @sales
WHILE @@FETCH_STATUS = 0
 BEGIN
   INSERT dbo.NewMonthSales
    (SaleID, saleyear, salemonth, currSales)
    SELECT
      NEXT VALUE FOR myseqtest
    , @yr
    , @mn
    , @sales
   FETCH NEXT FROM mycurs INTO @yr, @mn, @sales
 END

See possible answers