title: SQL - Inserting and Updating Multiple Records at Once
body: <p>I have a stored procedure that is responsible for inserting or updating multiple records at once. I want to perform this in my stored procedure for the sake of performance. </p>
  
  <p>This stored procedure takes in a comma-delimited list of permit IDs and a status. The permit IDs are stored in a variable called @PermitIDs. The status is stored in a variable called @Status. I have a user-defined function that converts this comma-delimited list of permit IDs into a Table. I need to go through each of these IDs and do either an insert or update into a table called PermitStatus.</p>
  
  <p>If a record with the permit ID does not exist, I want to add a record. If it does exist, I'm want to update the record with the given @Status value. I know how to do this for a single ID, but I do not know how to do it for multiple IDs. For single IDs, I do the following:</p>
  
  <pre><code>-- Determine whether to add or edit the PermitStatus
  DECLARE @count int
  SET @count = (SELECT Count(ID) FROM PermitStatus WHERE [PermitID]=@PermitID)
  
  -- If no records were found, insert the record, otherwise add
  IF @count = 0
  BEGIN
    INSERT INTO
      PermitStatus
    (
      [PermitID],
      [UpdatedOn],
      [Status]
    )
    VALUES
    (
      @PermitID,
      GETUTCDATE(),
      1
    )
    END
    ELSE
      UPDATE
        PermitStatus
      SET
        [UpdatedOn]=GETUTCDATE(),
        [Status]=@Status
      WHERE
        [PermitID]=@PermitID
  </code></pre>
  
  <p>How do I loop through the records in the Table returned by my user-defined function to dynamically insert or update the records as needed?</p>