Autoplay was a security hole on older versions of Windows, but can I trust newer version like Windows 8 or 10 to insert USB stick without turning autoplay off?
Although you ask specifically about Autoplay, I take your question more generally to be;
Can I trust newer operating systems like Windows 8 or 10 to [protect me when I] insert a USB stick?
To which I answer, NO, you can not trust any operating system to protect your computer from any USB of unknown origin. You should not plug in any untrusted USB into any computer if you are afraid of compromise. As @sebastian pointed out, there are USB devices that can physically harm your PC without autoplay, and there are also devices such as Rubber Ducky which can 'pretend' to be a keyboard and then type malicious commands (drop payloads) onto your machine. So not having autoplay on does not make you safe from viruses/malicious-code on usb sticks.
Graham Hill's answer is absolutely correct in my opinion!
I just wanted to add, that there are different ways where usb sticks could harm/infect your computer. Have a look at Bad USB for example.
In addition to n00b's answer above, I'd like to point out that autoplay is only disabled by default for removable devices. If you can reprogram the controller (think BadUSB), then you can make a flash drive that tricks the host computer into thinking that the flash drive is really a USB CD drive. There are actually legitimate uses for doing this - for instance, U3 USB drives (https://en.wikipedia.org/wiki/U3) use this to automatically run a launcher program from a read-only partition. (This was actually originally for compatibility with older systems lacking autoplay.)
To answer your question literally, an auto played data flash drive will not harm your computer in windows 10.
Provided your windows 10 is up to date and your virus definitions are up to date.
I did this just this morning, and it did not affect my PC.
I scanned and removed the virus with windows defender, then restored the files on the disk to their previous states using attrib command in privileged command prompt.