Link to home
Start Free TrialLog in
Avatar of megel6805
megel6805

asked on

Update Table from a temporary table

I have a table of real estate listings. For updating this data, I have gotten as far as manipulating the text file given to me and doing a bulk import into a second table, table_temp. It has the same structure as the main table. All of the rows have a unqiue MLS number and that column is in both the main table and temp table. Each day after the table_temp is loaded, it will have some records that are already in the main table, and the values will need updated, it will have some new records, and there will be some that are no longer there. How would I update the main table with the temp table in a stored proceedure and have it know whether to update each row (if the mls number already exists in the main table) or to insert the new row.

I will also need to delete any rows from the main table that were not in that day's temporary table. I was going to add a column to the main table called 'lastupdated' that would have the date the last time that row was updated or inserted in the stored proceedure. THen i could delete any rows that were not updated or inserted that day because they were not in the temp table.

Thanks in advance.
Avatar of chapmandew
chapmandew
Flag of United States of America image

Good question....it sounds to me that you want your tables data to look exactly like the data from the import....so, why don't you just remove all of the data from the table and insert the new data?
Avatar of megel6805
megel6805

ASKER

i would like to keep a few extra columns of data in the main table, and if anyone updates those values for the listing, they would remain in that row even if the other values are updated from the temporary table. also I don't want there to be a period of time where there are no values in the table. Is there some other way to do it?   goes through each row in the temp table and either updates values or inserts the new row?

If deleting then inserting them all is the best way then i can do that and keep the extra values elsewhere but i just want to find the best way to manage this type of situation.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes this got me on the right track and is 99% what I used to do my updates from my existing table. Thanks very much for the help.