title: understanding workings of cursor
body: <p>Trying to change some stores procedures and query's for better performance. For some part, it's rewriting cursor syntax. To do this,I must fully understand how they work. I tried this simple ETL example, but it does not give me the expected result. Basically, doing an UPSERT here with a cursor. </p>
  
  <p>Example code:</p>
  
  <pre><code>CREATE TABLE #Destination 
  (PersonID INT, FirstName VARCHAR(10), LastName VARCHAR (10))
   INSERT INTO #Destination VALUES (101, 'M', 'Donalds')
   INSERT INTO #Destination VALUES (102, NULL, 'Richards')
   INSERT INTO #Destination VALUES (103, 'Rianna', 'Lock')
   INSERT INTO #Destination VALUES (104, 'Leo', 'Svensson')
  
   CREATE TABLE #SourceTable 
   (PersonID INT, FirstName VARCHAR(10), LastName VARCHAR (10))
   INSERT INTO #Destination VALUES (102, 'Diana', 'Richards')
   INSERT INTO #SourceTable VALUES (103, 'Rianna', 'Locks')
   INSERT INTO #SourceTable VALUES (106, 'Cleo', 'Davung')
  
    DECLARE @PersonID INT
    DECLARE @Firstname VARCHAR (10)
    DECLARE @Lastname VARCHAR (10)
  
    DECLARE SimpleCursor CURSOR FOR 
    SELECT PersonID, FirstName, LastName 
    FROM #SourceTable
  
    Open SimpleCursor
    FETCH NEXT FROM SimpleCursor INTO @PersonID, @Firstname, @Lastname
    WHILE @@FETCH_STATUS = 0 
    BEGIN
    IF EXISTS ( SELECT PersonID FROM #Destination
                WHERE PersonID = @PersonID )
      UPDATE #Destination  
      SET #Destination.FirstName = #SourceTable.FirstName,
          #Destination.LastName = #SourceTable.LastName
      FROM #SourceTable
      WHERE #Destination.PersonID = #SourceTable.PersonID
    ELSE
      INSERT INTO #Destination
      SELECT PersonID, Firstname, Lastname FROM #SourceTable
  
    FETCH NEXT FROM SimpleCursor INTO @PersonID, @Firstname, @Lastname
    END
  
    CLOSE SimpleCursor
    DEALLOCATE SimpleCursor
  
  SELECT * FROM #Destination
  </code></pre>
  
  <p>What am I missing here? I am not updating anything, while PersonID 102 and 103 do exist.</p>
  
  <p>Thanks a lot.</p>