CREATE TABLE stg_account (
    acc_id   INT,
    balance  DECIMAL(12,2),
    status   CHAR(1)
);
--Target Table (With Constraints)
CREATE TABLE dwh_account (
    acc_id   INT PRIMARY KEY,
    balance  DECIMAL(12,2) CHECK (balance >= 0),
    status   CHAR(1) NOT NULL
);
--Reject Table (Manual)
CREATE TABLE reject_account (
    acc_id     INT,
    balance    DECIMAL(12,2),
    status     CHAR(1),
    error_msg  NVARCHAR(4000),
    reject_dt  DATETIME2 DEFAULT SYSDATETIME()
);
-- Sample Data

INSERT INTO stg_account VALUES
(1001, 1000, 'A'),   -- valid
(1002, -50,  'A'),   -- CHECK violation
(1003,  500, NULL),  -- NOT NULL violation
(1001,  300, 'A'),   -- PK violation
(1004,  200, 'B');   -- valid

--Procedure
CREATE OR ALTER PROCEDURE load_accounts_try_catch
AS
BEGIN
    BEGIN TRY
        INSERT INTO dwh_account (acc_id, balance, status)
        SELECT acc_id, balance, status
        FROM stg_account;
    END TRY
    BEGIN CATCH
        INSERT INTO reject_account (error_msg)
        VALUES (ERROR_MESSAGE());
    END CATCH
END;

--Procedure
CREATE OR ALTER PROCEDURE load_accounts_prevalidate
AS
BEGIN
    INSERT INTO dwh_account (acc_id, balance, status)
    SELECT acc_id, balance, status
    FROM stg_account
    WHERE balance >= 0
      AND status IS NOT NULL;
END;

--Procedure
CREATE OR ALTER PROCEDURE load_accounts_cursor
AS
BEGIN
    DECLARE
        @acc_id  INT,
        @balance DECIMAL(12,2),
        @status  CHAR(1);

    DECLARE cur_accounts CURSOR FOR
        SELECT acc_id, balance, status
        FROM stg_account;

    OPEN cur_accounts;
    FETCH NEXT FROM cur_accounts INTO @acc_id, @balance, @status;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            INSERT INTO dwh_account (acc_id, balance, status)
            VALUES (@acc_id, @balance, @status);
        END TRY
        BEGIN CATCH
            INSERT INTO reject_account (
                acc_id, balance, status, error_msg
            )
            VALUES (
                @acc_id, @balance, @status, ERROR_MESSAGE()
            );
        END CATCH;

        FETCH NEXT FROM cur_accounts INTO @acc_id, @balance, @status;
    END;

    CLOSE cur_accounts;
    DEALLOCATE cur_accounts;
END;
--<<load_accounts_try_catch
exec load_accounts_try_catch;
select * from dwh_account
select * from reject_account
-->>

--<<load_accounts_prevalidate
exec load_accounts_prevalidate;
--Violation of PRIMARY KEY constraint 'PK__dwh_acco__9A20D554015C98FE'. Cannot insert duplicate key in object 'dbo.dwh_account'. The duplicate key value is (1001).
-->>

--<<load_accounts_cursor
EXEC load_accounts_cursor;
select * from dwh_account
select * from reject_account
-->>
/*
Reject Table (reject_account)
Contains rows with errors:
negative balance (CHECK violation)
NULL status (NOT NULL violation)
duplicate primary key
Each row includes the actual SQL Server error message.

Why This Still Is Not Oracle LOG ERRORS

Even in the cursor-based solution:
processing is row-by-row, not set-based
performance degrades rapidly with volume
code complexity is high
transaction handling becomes fragile
Oracle achieves the same behavior in one SQL statement.

Final Technical Conclusion (SQL Server Example)

SQL Server treats DML errors as exceptions

Exceptions abort statements
There is no SQL-native row-level reject mechanism
The only functional approximation is cursor-based or ETL-based
One-line takeaway
SQL Server can approximate Oracle DML reject handling only through procedural or ETL-based solutions, both of which sacrifice performance, simplicity, and SQL purity.
*/

