I am trying to perform the following steps via stored procedure in SQL 2000. (I don't know if I am better off doing it programmatically or with a stored proceedure.) Any help on this would be greatly appreciated.
I have 2 tables in the same database, table x (my working table) and table y (my real data table)
1. I need to grab the ID number and update date from the first record in table x
2. I need to take that ID number and try to find a matching ID number in table y
3. If a match is found, determine if the update date in table y is older, if it is then update the record. If it is not, then delete the record in table x
4. If no match is returned from number 2 then insert the record into table y
5. Move to the next record in table x and repeat the above steps for that record.
I have the SQL written from 3 and 4 but I am not quite sure how to do 1, 2, and 5. Does anyone have any ideas on how to accomplish this? How do I take the value returned from #1 and use it for the searching value in #2? Thanks in advance.