For me it’s now primarily what @HumanRightsAreHumanRights has said, along with a need to pay my mortgage! However, there have definitely been other motivators along the way.
I’m in the process of writing my 30th novel for publication, which is part of a multi-book deal I’m currently contracted for. My initial desire to write came from an inherent need to tell stories, which I’ve been doing in some form or other, for almost as long as I’ve been able to read. The dream of being published first arose from wanting to emulate my writing heroes in some small way and to do the things that @HumanRightsAreHumanRights has described. As well as to have a book on the shelf, with my name on it, lined up next to novels by my favourite authors.
I make a ‘good’ living as an author and have sold more than a million novels, but it took nearly ten years of balancing writing with a day job before I became a full time author. So, if I’d been in the chase for publication purely for the money, I’d definitely have given up a long time ago! When people discover what I do, they often assume that all authors make a fortune and have no idea that multi millionaire novelists are the tiny exception and not the rule. There are certainly some aspiring authors who believe publication is the route to vast wealth, but someone has to be that exception so why not dream big?
As you know OP, there are highs and lows to the job, and I had some horrendous experiences with publishers early in my career, which drove me to the edge of giving up and if I’d known those things were going to happen, I might well have lost the desire to be published before I even started. I count myself incredibly lucky that I’m now contracted with the very best publisher around, and I have a hugely supportive editor.
As a PP said, in order to be published and continue to be so, authors may have to write what is commercially viable, which might be at odds with their passion projects. However, aspiring authors often have an overwhelming desire to write a story they believe needs to be told, regardless of whether it is likely to be commercially viable in practice. The motivation to write that story in the hope of finding a publisher for it remains, despite the low odds of doing so, because of the drive to tell the story itself.
A life changing diagnosis drove me to pursue my dream much more seriously than I ever had before, in case it was then or never, so I certainly understand the legacy aspect. However, I’m under no illusion that my very commercial fiction is the kind of great literature that will live on for generations. The very best bits of my job are the messages I receive from readers, telling me that escaping into my books has helped them through a difficult time in their lives, or that they feel seen because they identify so strongly with a character or plot line. It’s the knowledge that I’ve made a tiny difference to a stranger’s life, even for a little while, that has got my bum into the seat to write and want to continue to be published, even during the lowest lows in my career.
I guess your answer, OP, is that there are many different motivations that drive people to want to be published, which no amount of reality checks can diminish for those determined to pursue that goal. Then there are the dreamers, who won’t get any further than that, because the dream is by far the easiest part. While it remains just a dream, publication can also remain that unsullied perfect ‘destination’, which reality never gets the chance to bite. All of that said, I love my job and still find it amazing that I get to do what I’ve dreamt of doing my whole life, and I can’t imagine ever wanting to stop.