Link to home
Start Free TrialLog in
Avatar of Aspnet_9
Aspnet_9

asked on

Multiple Insert in SQL

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.
ASKER CERTIFIED SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America 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
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

SOLUTION
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
Avatar of Aspnet_9
Aspnet_9

ASKER

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/
ok