Sequence work with MERGE operation in SQL server

Yes, SQL Server SEQUENCE works perfectly with MERGE. When MERGE executes the WHEN NOT MATCHED THEN INSERT clause, each inserted row gets a value from NEXT VALUE FOR. Sequence guarantees uniqueness across multiple inserted rows, but it is not transactional, so gaps may occur if MERGE rolls back or server crashes. Using DEFAULT with sequence is recommended for cleaner design.

Let’s see the demo

--creating a sequence CREATE SEQUENCE Merge_Seq_Demo START WITH 1 INCREMENT BY 1;

Now creating a table

CREATE TABLE Merge_tbl_Demo (     CustomerID BIGINT DEFAULT NEXT VALUE FOR Merge_Seq_Demo,     CustomerName VARCHAR(50) );

Now creating stage table.

CREATE TABLE stgTbl (     CustomerName VARCHAR(50) );  INSERT INTO stgTbl VALUES ('Bagesh'),('Amit'),('Rahul');

Now we are merge statement to insert and update the records.

MERGE Merge_tbl_Demo AS T USING stgTbl AS S ON T.CustomerName = S.CustomerName WHEN NOT MATCHED THEN    INSERT (CustomerName)    VALUES (S.CustomerName);

Records inserted. See the records

Either we can use by default constrain or we can provide run time sequence number when we are inserting the records.