title: Copy data to another table before update or insert. Use stored procedure?
body: <p>I have two tables that I need to update in a database.  I am updating these tables from data in another database.  If a record in table 1 gets updated, I need the old information in table 1 to be stored in table 2.  I need logic in place to make a comparison and then update Table 2 if needed.  My question is, what is the best way to do this?  I am thinking a stored procedure would be the way to go, but I'm not sure.</p>
  
  <p>Here is a more visual explanation.</p>
  
  <pre><code>    Table 1
  Student    Grade
  james       6
  sarah       5
  
  
  
    Table 2
     EMPTY
  </code></pre>
  
  <p>Lets say that the data below is what I am pulling from another database.</p>
  
  <pre><code>   Other Database
    Student     Grade
     james        6
     sarah        4
     tom          7
  </code></pre>
  
  <p>Here is some sloppy logic that may help explain what I need to do.</p>
  
  <pre><code>--If records match do nothing 
  IF otherDatabase.student =  table1.student  AND  otherDatabase.grade = table1.grade THEN do nothing
  
  --If partial match copy old data to table 2 and insert new data to table1
  IF otherDatabase.student = table1.student AND otherDatabse.grade !=  table1.grade 
  THEN copy table1.student to table2.student AND copy table1.grade to table2.grade THEN UPDATE table1.grade from otherDatabase.grade
  
  --If no match copy new data to Table1
  IF otherDatabase.student != table1.student AND otherDatabase.grade != table1.grade THEN INSERT otherDatabase.student AND otherDatabase.grade INTO table1
  </code></pre>
  
  <p>In my example, James would not get touched, sarah would get moved to table 2 and then inserted into table 1 with new grade and tom would get inserted into table 1.</p>
  
  <p>I am very sorry if this doesn't make sense.  Please allow me to clarify if needed. Thanks</p>