I often need to fill up tables with random data to test data conversion scripts, foreign keys, indexes, etc. Here’s my “Go To” script for coming up with random data. I usually base it off this and change the name of the table to insert data into, but this has the things I need to remember, like an easy way to loop through code, NEWID(), RAND(), RND(), etc.
Declare @TestTable Table (ID uniqueidentifier, Num int, InsertDate datetime) Declare @icount int, @TopNum int, @Repeat int Set @icount = 1 ------------------------- ---Set the ranges here--- ------------------------- Set @TopNum = 10 --Highest number you want - note this will be 0 - 10 Set @Repeat = 1000 --number of times to repeat ------------------------- ------------------------- While @icount < @Repeat begin insert @TestTable Select NewID(), ROUND((RAND() * (@TopNum)),0), GETDATE() Set @icount = @icount + 1 End Select Num, count(Num) from @TestTable group by Num order by Num
[...] I’ve been doing some testing and in need of creating test data, so, not only did I create this script to populate a table, I’ve now been tweaking the password generator [...]
[...] showed this site off to a fairly new SQL guy and he’s used the generate random data script a lot and it’s saved him some testing [...]
[...] apparently I like scripts that create random data. I’ve written a couple of posts that create random numbers and random characters. This week I was working on a clean up job that would delete data that was [...]
Thanks for the comments on my blog. Good to see other ways of generating random data. I found a good random name generator which you might find helpful.
Here is the link:
http://www.fakenamegenerator.com/
They give you a wide choice of fields and even give you a SQL script to put the names into a database. There are other options but this one is good enough for me.