tonyyeb tonyyeb - 1 year ago 51
SQL Question

Using SQL to INSERT number of records based on QUERY

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 Source

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;


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.