Loop through IDs and inserting if not exists and updating if exists-CodePudding

I have an issue I need to resolve. I have two tables, one of them contains ID's of individual employees. The second table contains ID's of those employees and certain values. My task is to insert rows into the second table for each employee ID if they don't exist and update the value if they do exist. This is where I am starting but not sure how to proceed. Specifically, how to import the values of each ID or iterate through them.

CREATE OR ALTER PROCEDURE [procedure name]  
    @employeeID INT  
AS  
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
BEGIN  
    IF NOT EXISTS (SELECT 1 FROM table2 
                   WHERE testEmployeeID  = @employeeID AND testCode = 'someValue')  
        INSERT INTO dbo.table2 (testCode, testEmployeeID, 'testValue')  
        VALUES ('someValue', @employeeID, 'someValue2')
    ELSE 
        -- (update the values) 
END

CodePudding user response：

If you have two tables then a simple MERGE statement suffices. Like:

MERGE INTO dbo.table2 AS TGT
  USING ( SELECT testCode, testEmployeeID, testValue FROM dbo.table1
  ) AS SRC
    ON  SRC.testCode = TGT.testCode
    AND SRC.testEmployeeID = TGT.testEmployeeID
WHEN MATCHED THEN
  UPDATE SET testValue = SRC.testValue
WHEN NOT MATCHED BY TARGET THEN
  INSERT( testCode, testEmployeeID, testValue )
  VALUES( SRC.testCode, SRC.testEmployeeID, SRC.testValue )
;

If however the original comes at transactional level one row after the other then again a MERGE statement suffices as well. However in this case you need to construct your "record" in line directly or with a temporary table before the merge.

MERGE INTO dbo.table2 AS TGT
  USING ( SELECT 'someValue' AS testCode, @employeeID AS testEmployeeID, 'otherValue' AS testValue
  ) AS SRC
    ON  SRC.testCode = TGT.testCode
    AND SRC.testEmployeeID = TGT.testEmployeeID
WHEN MATCHED THEN
  UPDATE SET testValue = SRC.testValue
WHEN NOT MATCHED BY TARGET THEN
  INSERT( testCode, testEmployeeID, testValue )
  VALUES( SRC.testCode, SRC.testEmployeeID, SRC.testValue )
;

Performance wise the first option to compare two tables is better.