Search code examples
sqlsql-insertsql-scripts

SQL Script to Insert New Record Per Id

I have a SubQuestionScoreLink table that links a SubQuestion with all available Score options that will appear in a dropdown on the web site. As you can see from the example below currently 7 Scores are used per SubQuestion. I've created a new Score in the database that has an Id of 8.

Current Data

How would I go about creating a SQL script that will insert a new record per SubQuestion. I could achieve this in C# easily but think that's overkill.

Is the process something like the following:

  1. Get Distinct SubQuestion Ids
  2. Loop through Distinct SubQuestion Ids
  3. Call an insert statement like so
    INSERT INTO [dbo].[SubQuestionScoreLink]
               ([SubQuestionId]
               ,[ScoreId]
               ,[SortOrder])
         VALUES (@CurrentSubQuestionId ,8 ,0)

How would I do this as a SQL script?

Solution 
  • INSERT INTO [dbo].[SubQuestionScoreLink] ([SubQuestionId] ,[ScoreId] ,[SortOrder]) 
SELECT DISTINCT SubQuestionId, 8, 0 
FROM table