I am new to SQL.... I need a stored procedure that can update existing records and copy new records from one table in azure to another.
The staging table is dbo.OV_IncontactStage and the prod table is dbo.OV_Incontact. Contact_ID is the primary key on the prod table. The staging table only has new records or updated records.
What I currently have does not work exactly right because in the staging table, there are multiple records with the same unique identifier (Contact_ID). I only need the last modified one.
I did some reading on this and I believe there is a select Row_Number () Over (partition) statement to place an instance number on the records so I can get the most recent one if there are multiple, but I am not sure where to put this in the statement. Here is what I currently have
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spUpsertCallRecordv2]
Select ROW_NUMBER() OVER PARTITION by Contact_ID, Contact_Code, Start_Date
ORDER BY Start_Date DESC) AS Rownum,
--if succeeds MERGE dbo.OV_Incontact AS target_sqldb USING dbo.OV_IncontactStage AS source_tblstg ON ( target_sqldb.Contact_ID = source_tblstg.Contact_ID) When MATCHED AND RowNum = 1 Then Update Set Contact_ID=source_tblstg.Contact_ID, Contact_Code=source_tblstg.Contact_Code, Start_Date=source_tblstg.Start_Date WHEN NOT MATCHED THEN INSERT( Contact_ID, Contact_Code, Start_Date ) VALUES( source_tblstg.Contact_ID, source_tblstg.Contact_ID, source_tblstg.Contact_Code );