Adding MASSIVE testing data to your Database?
Tuesday, March 01, 2016 9:50 PM
Today I needed to add 250,000 users, what’s the fastest way to do this?
Today, I need to add a BOAT load of data to my db, 250,000 users. Yup, THAT many. Our VP wanted us to test our webpage with “a lot of users” and threw out 240,000. So why not try it out! Well, what’s the quickest way to get that many users into the db? I’m sure there are a few C# ways, PowerShell, but I had SQL Server opened and figured there HAD to be a looping way in sql. Yup!
declare @maxVal int = 250000;
declare @currVal int = 0;
while( @currVal < @maxVal)
insert into EventLog values (newid(), 10, getdate(), 2010, 'Habs\Carey.Price' + cast(@currVal as varchar),
'Habs1996', NULL, N'true', getdate(), NULL, NULL, '2016-01-12')
set @currVal = @currVal + 1;
select * from EventLog el where el.[user] like 'habs\carey.price%'
The deal here is, setup your upper max val, your loop invariant, do a while over those two vals. Next I’m doing a standard SQL insert but since I want names to be different, I’m tacking on the loop counter to the end, but don’t forget to cast it to a varchar otherwise you’ll get an error. Next, don’t forget to increase your loop invariant, otherwise you’ll be in an infinite loop. And then lastly, I try to see just the new data I added.
Now that you know one of MANY ways to quickly whip out testing data, it’s time to grab a coffee and get coding!