Well, the documentation states that these new columns were added from SQL 2016.  However, they are available in my SQL 2014 instance.


SELECT @@VERSION;


You could achieve this with a MERGE statement.
There is no need for an explicit transaction, as it is one one statement that will succeed or rollback as a whole.

The query works.

What is your expected outcome?

The format of your query appears to ba MySQL format.

In T-SQL, we can do it like this ...

Create some sample data

CREATE TABLE...

Please provide us with CREATE TABLE scripts for the 2 tables, as well as INSERT statements with sample data, and the expected results.

Although off topic, in SQL 2008 the query optomiser always assumes 1 record in @staging_table_var.  This is because SQL does not maintain stats for @Table variables.

If you change to...

You could try to get the Primary number first ...

WITH ctePhoneNumber AS (
SELECT
  pn.PersonID,
  pn.PhoneNumber,
  rn =...

Firstly, start off by using Jeff Moden's Tally function

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including...

You can also provide a Default value for the LEAD/LAG Window functions.

In this case, it means not having to add the extra AND PREVIOUS_CODE IS NOT NULL in the...