Link to home
Start Free TrialLog in
Avatar of ButtonDownBobby
ButtonDownBobby

asked on

SQL: Mass insert or Create a table with all the same values

basically I want to put say 1,000,000 rows with all the same data for each column into a table. I don't want to use a while loop with an insert; it will take to long.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Eyeballeth thy article on SQL Server:  T-SQL recipe to create a million sample people
Uses Itzik Ben-Gan's method to countinously double the size of a set until you get to a million.  Find 'Then let’s create our final SAMPLE_CUSTOMER table,' then read down.  Runs fast.
Then uses a cursor and lots of random numbers to create names, addresses, SSN's, phone numbers, etc.  Really dogs, but does the job.

I presented this at a SQL Saturday a couple of years ago and it went over really well.

I know you wanted to avoid the WHILE loop.  I think there are a couple of queries out there that queries various sys tables that can easily have over a million rows to more quickly spin up a set.

Hope this helps.
Jim
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ButtonDownBobby, do you still need help with this question?