Android - Good idea to transfer all phone numbers from SIM card to google contacts?

I'm not going to answer if it's a good idea or not - you need to solve that for yourself :).

Having your contacts stored with Google Contacts will eliminate any duplicates (at least duplicates where you have the number stored in the SIM and e-mail in Google, or something like that) and allow you to manage/sync those contacts from anywhere you can access Google Contacts, like a computer. You are correct that it will 'backup' your contacts so that if you were to switch phones or even carriers, you wouldn't have to worry about bringing your contacts as long as the phone you have supports Google Contacts (web or app).

Keeping your contacts on the SIM allows you to keep them from Google (if you're worried about privacy), and if you get another SIM-based phone, you can easily move them (provided you keep the same SIM).

Personally, having my contacts in Google Contacts has been great. Easy synchronization, use photos from Picasa as contact photos, management from a computer... but you will have to decide for yourself which is "better".

You may want to see this answer to help you move your contacts from the SIM to Google Contacts.

I can't think of any reason why this wouldn't be a good idea.