Using SQL to INSERT number of records based on QUERY

Question

I have a table which has a number in a field. Based on that number I want SQL to INSERT records into another table. Below is an example of the first table:

Event    Fields   Type
Swimming 3        Basic
Swimming 2        Advanced
Running  5        Basic
Running  1        Advanced

So based on the table above I would like SQL to INSERT the records below:

Event    GradeType    Grade
Swimming Basic1       NULL
Swimming Basic2       NULL
Swimming Basic3       NULL
Swimming Advanced1    NULL
Swimming Advanced2    NULL
Running  Basic1       NULL
Running  Basic2       NULL
Running  Basic3       NULL
Running  Basic4       NULL
Running  Basic5       NULL
Running  Advanced1    NULL

I think I need to use WHILE to loop through but haven't a clue where to start! All help appreciated.

Answer 1

There is no need for loop at all:

INSERT INTO new_table(Event, GradeType, Grade)
SELECT Event, CONCAT(Type, n) AS [GradeType], NULL AS Grade
FROM tab
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n(n)
WHERE n <= Fields;

LiveDemo

Keep in mind that n subquery is tally table with hardcoded values. You could use any other method that is suitable. Related: What is the best way to create and populate a numbers table?

If you are using SQL Sever lower than 2012 you need to use + to concatenate.

Answer 2

Just Building on Lad2025 Answer.If you are below 2012(specifically 2005 as values clause is not supported) you can use below query..Assuming you have numbers table ,otherwise here is the way to populate it

SELECT Event,
 Type+ cast(n  as varchar(5)) AS [GradeType],
  NULL AS Grade
FROM tab t
CROSS APPLY (select n from numbers nmbr where  nmbr.n<=t.fields)  b