How do I run a stored procedure only on newest data loaded?

Question

My database has a table where the data is loaded from an Excel upload. Then, a stored procedure is run on the data to validate it and insert it to another table.

Currently, my stored procedure will run the validation on all the data again when it's executed.

How do I make it so that it runs the query on the newly loaded data only?

My current stored procedure has the following query:

INSERT INTO dbo.Data_Pass(
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
)
SELECT 
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
FROM dbo.SOURCE_TBL AS ST
JOIN UPLOAD_DATA AS UD ON ST.ACC_NO = CAST(UD.ACC_NO AS nvarchar)
    AND ST.CTRY = CAST(UD.CTRY AS nvarchar)
WHERE ST.[PRDCT_CODE] = '102'
    AND CAST(UD.[PRDCT_CODE] AS nvarchar) LIKE '%%2' 
    AND ST.Val LIKE ('%' + CAST(UD.CorrectionVal AS nvarchar)+ '%');

UPDATE Data_Pass
SET REPORT_TY = 'Correction',
    CHECK_DT = GETDATE(),
    CHECK_SRC = 'Correction Report', 
    Data_Pass.STATUS = 'Validated'
WHERE REPORT_TY IS NULL AND CHECK_DT IS NULL AND CHECK_SRC IS NULL 

I also have an id field in Data_Pass which is a primary key and is auto incremented.

The loaded data can be around 200,000 or sometimes more in one go, so I'm looking for the most efficient way to do this.

Edit: Added in my create table statement:

CREATE TABLE [dbo].[Data_Pass](
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [ACC_NO] [nvarchar](50) NULL,
    [ORGN] [nvarchar](50) NULL,
    [DEST] [nvarchar](max) NULL,
    [CTRY] [nvarchar](max) NULL,
    [CRNCY] [nvarchar](50) NULL,
    [PRDCT_NAME] [nvarchar](50) NULL,
    [PRDCT_CODE] [nvarchar](50) NULL,
    [DETAILS] [nvarchar](max) NULL,
    [REPORT_TY] [nvarchar](max) NULL,
    [CHECK_DT] [datetime2](7) NULL,
    [CHECK_SRC] [nvarchar](max) NULL,
    [STATUS] [nvarchar](50) NULL
)

| id  | ACC_NO    |  ORGN | DEST | CTRY |  CRNCY |  PRDCT_NAME  | PRDCT_CODE |  DETAILS |  REPORT_TY   |  CHECK_DT  |      CHECK_SRC      |   STATUS   |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 979 | 900000000 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 980 | 900000001 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 981 | 900000002 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 982 | 900000003 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 983 | 900000004 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   09:09.0  |   Correction Report |  Validated |

Answer 1

_{Community wiki answer - please edit to improve:}

Don't load directly to dbo.Data_Pass.

Instead, load to a staging table, and run the SP against that table. Then insert the final, validated data into dbo.Data_Pass.

Answer 2

By "run the validation on all of the data again" I assume that you mean the UPDATE statement that you included in your question. The simplest way is to solve the problem is to not run the UPDATE at all. You don't specify insert values for the [REPORT_TY], [CHECK_DT], [CHECK_SRC], and [STATUS] columns so the rows you insert will always have a value of NULL for those columns in the Data_Pass table. Change your insert query to:

INSERT INTO dbo.Data_Pass(
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
  ,[REPORT_TY]
  ,[CHECK_DT]
  ,[CHECK_SRC]
  ,[STATUS]
)
SELECT 
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
  ,'Correction'
  ,GETDATE()
  ,'Correction Report'
  ,'Validated'

If that's not an option for some reason then you can take advantage of the clustered index on the identity column, as long as you aren't manually messing with that value. If all of the rows that you insert have a greater value for [id] then all of the rows that already exist in the table then you can save off the value of [id] before the insert and use it as a filter for the update query. Something like:

DECLARE @max_id INT;

SELECT @max_id = MAX(Id)
FROM [dbo].[Data_Pass];

INSERT INTO dbo.Data_Pass(
...
AND ST.Val LIKE ('%' + CAST (UD.CorrectionVal AS nvarchar) + '%'));

UPDATE Data_Pass
SET REPORT_TY = 'Correction',
    CHECK_DT = GETDATE(),
    CHECK_SRC = 'Correction Report', 
    Data_Pass.STATUS = 'Validated'
WHERE REPORT_TY IS NULL AND CHECK_DT IS NULL AND CHECK_SRC IS NULL
AND Id > @max_id;