Would it be an appreciable difference in sound?

Right now I am using my Rotel RDV-1060 to play audio cds. If I buy a CD player like Marantz 6005 or a used musical fidelity etc. Will it result in huge improvement?
BTW other components are Lexicon RX amp, MC-12, Meridian 557 amp and Usher CP-8871 speakers.
Maybe. But it can go either way. You may end up liking the Rotel better.
For a "HUGE" improvement, I think you need to move up to the next level of CDPs. These are typically CD/SACD players or CD ONLY and don't include DVD playback.
The higher up the chain, CDPs will (sometimes) have a better DAC, and most certainly a better master clock to prevent jitter. Lack of jitter control is what causes that "glare" or harshness from a CD.

For example, the Musical Fidelity M6CD would fall into this category, and the higher end Marantz. If I was to put a price on this level of CD, it would be $2000+. Of course there are some exceptions.
It's a gamble. If the cd player you add matches well with the gear you already have, you'll be able to hear the true potential of the player. If it's a poor match, you won't hear the true potential of the player. Beneficial matching is huge in this hobby.
Which player will match given my gear?
Unfortunately, there's no sure answer for which player. Since you have Lexicon gear, you might want to try a Lexicon player to go with your pre and amp. You stand a better chance of achieving synergy when staying with the same manufacturer. (if you like the house sound of Lexicon) There is more of a chance of a match. Other than that, you will need to try and audition different players with your setup to find one that improves on the sound of your dvd player. Hit and miss. If I were you, I'd try to purchase with the ability to return so if the cost isn't justified by an improvement in sound, you can return the player.