I think that's a great point that the memories will live on. I don't know whether we have a soul or not but our greatest legacy that may live on for hundreds or thousands of years is our impact on others. The ideas, accomplishments and memories of the greats like Isaac Newton and Einstein among others will live on for a long time to come.

What does give me comfort is knowing that every moment is an eternal fixed point in space time and that every NOW is inline with another NOW in space time as discussed in the below vid.
https://www.youtube.com/watch?v=vrqmMoI0wks&index=30&list=PLB0EB1638F706BC49

Happiness and sadness are two ends of the same stick like night and day, black and white. One has no value without contrast to the other. If sadness didn't exist, there would be nothing to compare happiness to and therefore it wouldn't exist. If you look at the ying and the yang, you can't see the white dot without the black background and vice versa.

If it were possible to be happy 100% of the time, there would be no reason to live because everything would make you happy and there would be no reason to improve anything. You could be swimming through a sewer and still be happy. Sadness and negative emotions can be beneficial and outline to us what is and isn't fulfilling to us.

In my opinion, fulfillment is the goal and it is wise to acknowledge that there will be ups and downs as you acclimatise to each level of success and sometimes you will take 3 steps forward and 2 steps back. Even if you become a billionaire, you will acclimitise to that level and if you start to lose money, you may become depressed. I know of at least one billionaire that killed himself because of this phenomena.

To answer your questions, I will first break it down into three parts:
1. why do we do what we do?
2. what is inspiration?
3. what is motivation?

1. Humans base their decisions and actions to fulfill their highest values or what is most important to them. Our biggest "perceived" voids create our biggest "perceived" values. For example, like @Bitter Crank mentioned, if a car becomes too hot or cold, you have a perceived void of a more pleasant temperature. This also works on a larger scale. For example, a lot of people that become wealthy have relatively poorer upbringings which can create a void/ value on building wealth. On the other hand, if you have an upper-middle class upbringing, you may not have as high of a value on money and may simply follow in your parents footsteps to a similar upper-middle class life.
2. Inspiration arises when we are fulfilling what is truly meaningful to us. Think of the Statue of David, The Mona Lisa, The Geodesic Dome, Frank Lloyd Wright's Falling Water etc. These great masterpieces came from people that were doing what was truly meaningful to them. On the other hand, if an architect like Frank Lloyd Wright was instructed to become a make up artist, he would have very low inspiration and wouldn't perform nearly as well and be considered a "failure". In this case, a boss may try to "motivate" the employee.
3. Motivation is an external force required to do something that is not really that important to you. A boss may try to motivate you with money. You may try and motivate yourself to workout with a motivational video or motivate yourself to study for a degree your parents told you to do etc. Sometimes motivation can be helpful, but if you are not mainly doing what is important to you, you will live an unfulfilling life.

Ultimately, the goal is to live an inspired life, fulfilling what is most meaningful to you. If you need motivation to do something, it is not that important to you or you haven't chunked down what you are trying to achieve into manageable bites.