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

All Courses

From novice to tech pro — start learning today.