SQL insert statement

I have a table 'test' with columns

Id,          name,          notes,         definition,      url,      created_on      updated_on
6232      samxx      xxx              xxx              xxx       xxx                          xxx

so now using cursor I want to insert 10,000 rows into 'test' table with same values. As"id" is the identity
column it will generate Id'd automatically.
gaurav sharmaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Eyeballeth thy article on SQL Server:  T-SQL recipe to create a million sample people, which is an image and code-heavy tutorial on how to spin up sample data using cursors and random numbers/names/demographics.

If you like what you see please click the big 'Was this article helpful?' button at the the bottom.

Good luck.
gaurav sharmaAuthor Commented:
Hey jimThanks for the reply and yes it was intended to do using cursor.. and also I have to insert this 10000 rows for an  existing table lets say I have to insert it from 5000( it has already 5000 rows in it)
Anthony PerkinsCommented:
Is there some reason you want to do it in a loop?  Why not do it with a single INSERT statement?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Anthony PerkinsCommented:
You should be able to do it like this:
        SELECT    1 c
        UNION ALL
        SELECT    1
        ), -- 2 
        CTE1 AS (
        SELECT    1 c
        FROM    CTE0 a,
            CTE0 b
        ), -- 4 
        CTE2 AS (
        SELECT    1 c
        FROM    CTE1 a,
            CTE1 b
        ), -- 16 
        CTE3 AS (
        SELECT    1 c
        FROM    CTE2 a,
            CTE2 b
        ), -- 256 
        CTE4 AS (
        SELECT    1 c
        FROM    CTE3 a,
            CTE3 b
        ), -- 65,536 
    Numbers AS (
        SELECT    ROW_NUMBER() OVER (ORDER BY c) Number
        FROM    CTE4

	INSERT	test (name, notes, [definition], url, created_on, updated_on)
    SELECT  'samxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx'
    FROM    Numbers
	WHERE	number BETWEEN 5000 AND 14999

Open in new window

Of course if you already have a "tally" table already then it becomes trivial:
INSERT	test (name, notes, [definition], url, created_on, updated_on)
SELECT  'samxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx'
FROM    TallyTable
WHERE	number BETWEEN 5000 AND 14999

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you need a cursor when you can do it in one shot INSERT?
Jim HornMicrosoft SQL Server Data DudeCommented:
Curiosity overwhelms me ...


Why did you accept the one line comment you did, which is essentially a copy of an earlier comment, and not any of the more detailed solutions provided?


Also, why are you asking another question inside a question that you've already selected an answer?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.