We would like to move the users from a one Identity system to WSO2 IS. Both userstores reside on a SQL DB. We would use SELECT INTO (INSERT INTO SELECT) at the database level to move the users from the old Identity Store to WSO2 IS. Will this cause any issues with the data integrity? Or should we just use the WSO2 IS APIs instead?
You have your users in separate user DB (say foo user store) and you need to move all users in to WSO2IS user store schema..? It mostly depends on the how user's password has been stored in the foo user store.
If password is stored as plain text (or encrypted as it can be decrypted), you can WSO2IS APIs to add users which is mostly the best option. You can write some tool to read from foo DB and call the API of WSO2IS. User Mgt API can be used for it. More details from here
If password is stored as hashed, then we can not use the APIs and we need to use some database level migration. You can move to username and user's password in to UM_USER table and user's attributes in UM_ATTRIBUTE tables.
But, please note, if your foo DB has used some different password hashing schema which is not supported by WSO2IS, above migration also would not be success out of the box. By default WSO2IS supports for SHA-1, SHA-2 hashing methods only. If it is not, you may need to do some customization.
However, why you are trying migrate it to WSO2IS schema? Can't you write a custom user store manager implementation for your foo DB and connect WSO2IS in to your existing user store ? I guess, it is more convenient than migrating.