I don't think there is any evidence that all the Sith knew the power of transfering their spirit. First, I don't recall Palpatine saying that every previous Sith Lord had transferred their spirit to their apprentice. What he said was:
PALPATINE: Kill me, and my spirit will pass to you. All the Sith live in me. You will be Empress. We will be one.
The Rise of Skywalker
He also later claimed that "I am all the Sith."
When he says that "all the Sith live in" him, I expect he is speaking metaphorically, saying that he has the power of all the Sith, since he is on the Sith planet of Exegol with the apparent support of the remnants of Sith spirits that are there, in much the same way as Rey says that she is all of the Jedi when their Force echoes are backing her up. Or, although the context makes this less likely, it could just be a straight boast. Remember "I am the Senate"?
In fact, taking this to imply that every Sith has performed an essence transfer of the sort Palpatine proposes would contradict a number of things, such as the fact that Palpatine is not Plagueis, or that the Rule of Two involves an apprentice killing their master.
But another possibility is that, after arriving at the Sith planet of Exegol, he did take up the spirits of the Sith, who may have bound themselves to that place as the Dathomir witches bound themselves to their cave, or as Lord Moomin bound himself to his mask.
Yet another possibility is that when Palpatine says that his spirit will pass to Rey upon his death, he doesn't mean his consciousness, but rather his power and evil in the Force, as some have proposed. In such a case, perhaps this is what happens whenever an apprentice kills their master, but it's not exactly immortality of the sort that the Sith desire. Palpatine, who at this point is not exactly in good shape, may have viewed dying as a worthwhile sacrifice to inaugurate a powerful new Sith Lord.
As for the "Tragedy of Darth Plagueis the Wise," we have very little idea how honest Palpatine was being in that scene. In fact, we know he was lying at some point, because at various times he claimed the following:
Plagueis taught his apprentice everything he knew, presumably including the power to cheat death. He heavily implied that he could teach Anakin the secret: "not from a Jedi".
That he didn't know the power, and that if he and Anakin worked together, he could discover it.
Obviously, these are inconsistent. Throughout the entirety of Revenge of the Sith, he is twisting the truth to persuade Anakin, and we can't take any of these statements at face value. Maybe he knew Plagueis's methods of cheating death at that point. Maybe he didn't.
If Palpatine didn't actually learn how to cheat death before Revenge of the Sith, at some point during the 50 years between then and the new trilogy, he may have discovered how. The end of Revenge of the Sith suggests, if Palpatine is being honest, that he intends to pursue such investigations with the help of Vader, and canon works like Lords of the Sith and Tarkin suggest that this is exactly what he was doing. This was also the purpose of Project Blackwing. We know, moreover, that he intended not only to avail himself of this limited form of immortality, but to attain a greater form and become all-powerful.
And he would not allow himself to be sidetracked from his goal of
unlocking the secrets many of the Sith Masters before him had sought:
the means to harness the powers of the dark side to reshape reality
itself; in effect, to fashion a universe of his own creation. Not mere
immortality of the sort Plagueis had lusted after, but influence of
the ultimate sort.
As his Empire swelled, bringing more and more of the outer systems
into its fold, so too would his power unfurl, until every being in the
galaxy was held captive in his dark embrace.
In other words, Palpatine wanted to go beyond anything that previous Sith had attempted to attain immortality. Note that this also suggests that (a) not all the Sith Lords knew the secret of immortality, (b) not even Plagueis knew it to his satisfaction, (c) Sidious and Plagueis are separate people.