I need to write a query which repeats the rows by incrementing the values

Hi all,

         I have a table with three columns say ID with identity, name and location and for this columns say I have values as (1,'RAM','CA'), (2,'SAM','IA'),(3,'PAM','MI') etc.... Now want to get 100 rows, say after 3rd row, the 4th row should again start with same name and same location but adding one value to it, say 4th row will be values (4,'RAM1','CA1'), similarly 5th row will be (5,'SAM1','IA1') and 6th row will be (6,'PAM1','MI1'). Again for 7th row it should give values as (7,'RAM2','CA2') and so on. So it should continue like this till 100th row, for that I need to write the code, I could not able to figure it out, can you please help me in this.

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.

Hello Aparanjit

Please refer to this sample...Is this the sort of thing you are after.  How many rows are in your data now?  3? or was that just for the question.  

SQL Example for increment

else if your table is empty but planned, maybe you could also do it in excel and then just import the 100 records to the table?
This may provide some ideas, not sure if you want 100 records for each name, or 100 records in total, but this produces 300 records in all (100 each):
    	([ID] int, [name] varchar(20), [location] varchar(20))
    INSERT INTO Table1
    	([ID], [name], [location])
    	(1, 'RAM', 'CA'),
    	(2, 'SAM', 'IA'),
    	(3, 'PAM', 'MI')

**Query 1**:

    cte as (
             select id, name, location, 1 as counter from table1
             union all
                    id + counter + 3
                  , name --+ cast(counter as varchar)
                  , location --+ convert(varchar, counter)
                  , counter + 1
             from cte
             where counter < 99
          , name
          , location
    from table1
    union all
          , name + cast(counter as varchar)
          , location + convert(varchar, counter)
    from cte
    order by name

Open in new window

see: http://sqlfiddle.com/#!3/277fe/12

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
Thank you for the solution acceptance - but I'm curious why did you award only a B grading?
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

From novice to tech pro — start learning today.