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?
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 Developer, Architect, and AuthorCommented:
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.
Jim
0
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)
0
Anthony PerkinsCommented:
Is there some reason you want to do it in a loop?  Why not do it with a single INSERT statement?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Anthony PerkinsCommented:
You should be able to do it like this:
WITH    CTE0 AS (
        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

0

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?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Curiosity overwhelms me ...

1.

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?

2.

Also, why are you asking another question inside a question that you've already selected an answer?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.