Yesterday I needed to move some data between a table in an old version of a database and a new version of a database for testing, just some records to play around with. This is usually just as simple linking the databases and doing an INSERT with a SELECT statement to populate the table. For example:
However, the users table for this old version were specific to the application. The new version of the application hooks to a users table that is used by multiple applications. So running this will give an error about FK constraints because the User Ids from the old database not exist in the new one.
So, no big deal, this is just test data right? I can just assign random Ids from the Users table in the new database.So I modified the above SQL just slightly and ran:
The important change here is
(SELECT TOP 1 id FROM Users ORDER BY NEWID()) AS StartedBy
This is how you choose a random row in MSSQL. However, when I used it as a subquery in this insert, it actually only chose ONE random row, and inserted it 1000 times. Clearly this is not what I had in mind, while it's just test data, I'd like it somewhat plausible.
So, I stumbled upon a How to request a random row in SQL?" on StackOverflow that guided me to the answer. Here is the result:
This actually selected a random user id for each individual record that was inserted into the destination table. There is decent explanation about what is going on in this query on the SO post so I encourage you to check that out if you are curious.
Just one of those things that seems straight forward but turns out to be a little more complicated.