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.
Microsoft VisioMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Aspnet_9

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Aaron Tomosky

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ephraim Wangoya

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
Ramesh Babu Vavilla

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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?
penyCuicas

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ramesh Babu Vavilla

if you are looking for stress testing SQL Server then you can use this free tool
http://www.sqlstress.com/
Aspnet_9

ASKER
ok