We help IT Professionals succeed at work.

Multiple Insert in SQL

463 Views
Last Modified: 2012-06-22
I want to insert 1000 rows in a table in SQL Server 2008 for load testing.
How can i generate the script for inserting 1000 or more records at a time.
Comment
Watch Question

Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
no need for a loop, try

SELECT number 
FROM master. . spt_values 
WHERE type = 'P' AND number BETWEEN 1 AND 1000

Open in new window


you can insert into your table as follows

insert into yourtable
SELECT number 
FROM master. . spt_values 
WHERE type = 'P' AND number BETWEEN 1 AND 1000

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Would this query of inserting 1000 records at a time impact the performance of SQL Server???
Later on I want to insert 1000000 records with the query how abt that?
you can use a common table expression like this



WITH cteNumbers      (Number) AS
(
      SELECT Number = 1
      UNION ALL
      SELECT Number = Number + 1
      FROM cteNumbers
      WHERE Number < 1000
      
)
SELECT *
FROM cteNumbers
OPTION (MAXRECURSION 0);

If you want to insert 1000000 records last about 15 seconds.
if you are looking for stress testing SQL Server then you can use this free tool
http://www.sqlstress.com/

Author

Commented:
ok
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.