How to use Window Function in MERGE statement?

Question

I'm creating a SQL Script in Azure Synapse to transform dimension data from a Staging table [Source] to its Storage table [Target].

The target is created with a key using ROW_NUMBER() function:

IF NOT EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'prd' and t.name = 'dim_stations')
    CREATE TABLE prd.dim_stations
    WITH
    (
        DISTRIBUTION = HASH(station_key),
        CLUSTERED COLUMNSTORE INDEX
    )
    AS  SELECT 
            ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC) AS station_key, 
            [station_id],
            [station_name]          AS name,
            [station_latitude]      AS latitude,
            [sattion_longitude]     AS longitude
        FROM
            stg.stations;
GO

I'm trying to handle SCD Type 1 in the table using MERGE but I can't handle the key in the Insert Statement with an error that means Window Functions Could Only be Used with SELECT Statement.

The Merge Statement:

MERGE INTO prd.dim_stations AS p 
USING stg.stations AS s
ON s.station_id = p.station_id
WHEN MATCHED
THEN UPDATE SET
    p.latitude = s.station_latitude,
    p.longitude = s.sattion_longitude,
    p.name = s.station_name
WHEN NOT MATCHED BY TARGET THEN
INSERT (station_key, station_id, name, latitude, longitude)
    VALUES
    (
        ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC),
        s.station_id,
        s.station_name,
        s.station_latitude,
        s.sattion_longitude
    )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OPTION(LABEL= 'MERGE Stations')

I really appreciate any help you can provide.

Answer 1

I have tried this approach with out using the SELECT statement in the Merge.

For example I have created 2 table Called the Source01 and Target01 to implement SCD Type 01 which will perform the update and Insert. Source:

CREATE  TABLE Source01 (
id INT,

name VARCHAR(50),

age INT,

city VARCHAR(50)

);

Target Create statement:

CREATE  TABLE Target01 (
id INT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
row_num INT,
CONSTRAINT PK_Target01 PRIMARY  KEY  NONCLUSTERED (id) NOT ENFORCED
) WITH (
DISTRIBUTION = HASH(id),
CLUSTERED COLUMNSTORE INDEX
);  
INSERT  INTO Target01 (id, name, age, city, row_num)SELECT
id,

name,

age,

city,

ROW_NUMBER() OVER (ORDER  BY id) AS row_num

FROM Source01;

I am using the ROW_NUMBER() function just over ID column. in the target table.Before perform the Merge statement row count 14.

Now at the source01 I am updating 2 records. and Inserting 1 row.

-- Update age and city for John Doe
UPDATE Source01
SET age = 26,
city = 'Chicago'
WHERE id = 1;
-- Update age and city for Jane Smith
UPDATE Source01
SET age = 33,
city = 'San Francisco'
WHERE id = 2;
-- Insert a new record in the Source table
INSERT  INTO Source01 VALUES(16, 'Samantha Green', 31, 'Austin');

Before the Source table is updated Using the MERGE statement. Update 2 records in the Target table and Inserting new row.

MERGE Statement:

MERGE  INTO Target01 AS T
USING Source01 AS S
ON T.id = S.id
WHEN  MATCHED  THEN
UPDATE  SET
T.name = S.name,
T.age = S.age,
T.city = S.city
WHEN  NOT  MATCHED  THEN
INSERT (id, name, age, city)
VALUES (S.id, S.name, S.age, S.city);

After Update