Link to home
Start Free TrialLog in
Avatar of 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.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
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?