CDs are 16 bit/44.1kHz and SACD is 24 bit/96kHz. SACD has 64 times the resolution of a CD. If you're into jazz or classical, then SACD is a good way to go. There are about 7,000 SACD recordings and probably 90% are classical and another 7%-8% are jazz. I always buy them over CDs or LPs.
SACD is a niche product, but has done well enough to stay alive for more than ten years. It picked up where reel-to-reel left off - for people who want archival-quality recordings. (Reel is awesome, too, but player maintenance is an expensive pain and the tapes deteriorate over the years.)
SACD players are often terrific for CD playback. I play CDs all the time and think my player sounds great with either.
SACD players won't upsample CDs. If you want to do that, however, a number of DACs will upsample CDs.