Im currently trying to convert parts of my "old" tables... therefore i need to iterate over all "players" in order to generate a unique ID for each of them to insert them into another table... if the generated unique ID already exists in the other table, it should try it as long as it found one that fits... i never did that before in SQL, any tipps where i could begin ?
@genaray Not terribly clear, sorry. You have an old table and you have a new table. Rows from the old table need to be moved to the new table. But the new table already has some rows. Because of that, the old rows cannot simply be inserted, because their IDs may conflict with the IDs of rows already existing in the new table. So new IDs need to be generated for those (= the old rows with conflicting IDs). But when there's no conflict, old IDs may (or should?) be kept. Is that what's happening?
@AndriyM Well, it should try to insert the item into the new table till it was sucessfull... if the id already exists, it should generate a new one and repeat the insertation
@AndriyM I have no clue... im a game engineer :/ i only had database lessons about the basics... Thats why i asked my question here
@AndriyM It is... but each entity already own a unique bigint primary key... the highest one is already somewhere near the limit, so insertation with auto generated keys is probably not the best decision, it may exhaust its limits
in general i would assume its working this way : loop over all rows, generate a id and try to insert it into the new table for each row, if a exception occured -> repeat process with new id
But i have no clue how i could translate this to sql... i bet this can not be represented as a single select/insert query
It might be possible but the select part would likely be a UNION ALL query, where one leg would select rows with non-conflicting IDs and the other select and replace the conflicting IDs.
In any event, there are at least two additional separate problems here: 1) how to determine unused ID values so that they can be re-used, and 2) how to re-use them i.e. how to substitute them for the old IDs.
I mean, I would consider those as additional problems if I were to solve this in a set-based manner (which is how I prefer to solve database problems)
This looks a little too much for a quick answer anyway. Seems worth posting as a question on the main, in my opinion.
Especially if the version used is < 8.0. Those really require you to have experience handling such issues, given their limited capabilities (language-wise at least).
Here it is... in case that anyone is interessted : https://dba.stackexchange.com/questions/273474/transfer-rows-into-a-new-table-forcing-to-generate-unique-primary-keys-till-its
@genaray Adding a fiddle (dbfiddle.uk, SQL Fiddle, DB Fiddle) with a sample setup goes a long way in questions of this kind. People are more likely to try and help if they have something to work with.