Yes a drive like that will work fine - I’ve just finished transferring all my CDs into Apple Music using this external DVD drive:
https://www.amazon.co.uk/dp/B0CQT96C93
…which is very similar.
External USB drives tend to be slower and more clunky than internal ones, but since our Lords and Masters have decided that physical disks are old hat and we don’t need CD or DVD drives any more, we are pretty much stuck with external ones.
The quality of the copies are dependent on the settings in the software that you use to transfer the songs, and the file format you save them in, rather than the drive itself.
For best quality you want to keep the songs in a format as close to the original as possible i.e. 44.1kHz sample rate and 16 bits; MP3 files are compressed and downsampled from that so will sound worse. I would not recommend MP3 these days.
Purists will suggest saving in FLAC format; personally I save to AIFF (uncompressed) since my computers are all Macs and I am not short of hard drive space.
If you use Apple Music or iTunes there is also Apple’s AAC format or Apple Lossless, both of which are compressed so take up less drive space.
In Apple Music look in Settings / Files / Import settings for these options, and set the preferred defaults before you import anything!
On Windows you can use WAV format if you like instead of AIFF or FLAC.
There is no point in “upsampling” audio form a CD to a higher sample or bit rate such as 96/24 as you can’t get better quality than what you put in.
I still like to listen to actual CDs (via an Audiolab CD transport and an Audalytic AH90 DAC) in my hifi system, but having everything in Apple Music means I can play anything on my phone if I’m travelling.
If you don’t have an actual hifi CD player and don;t want to bother with one you can connect your computer to a DAC via USB and thence into the “aux” input on a hifi amplifier, thus bypassing the rather basic audio DAC built into the computer.
Many DACs like the AH90 can also be used as headphone amps without a full stereo system.
Hope that helps!