title: Insert into a child table for every row in a parent table
body: <p>My DB looks like this</p>
  
  <pre><code>tblAudits
  PK AuditID
  AuditStatus nvarchar(50)
  
  tblQuestionDefs
  PK QuestionID
  
  tblAuditAnswers
  PK AnswerID
  FK AuditID
  FK QuestionID
  </code></pre>
  
  <p>I've recently added some new questions to the audits and I want to add an answer record for each new question to all active audits.  What is the best way to do this in SQL?  I know I can use <em>cursors</em> or <em>loops</em>, but those are both very slow AFAIK.  Any set-based way to do it?</p>
  
  <p>Here's the cursor based code</p>
  
  <pre><code>declare
      @AuditID int
  
  declare AuditsCursor cursor fast_forward for
      select AuditID from tblAudits where AuditStatus='Issued' or AuditStatus='Pending' order by AuditID desc
  
  open AuditsCursor
  fetch next from AuditsCursor
  into @AuditID
  
  while @@FETCH_STATUS = 0
  begin
  
      insert into tblAuditAnswers (AuditID, QuestionID)
      select @AuditID as AuditID, QuestionID
      from tblQuestionDefs
      where QuestionID not in (
          select QuestionID from tblAuditAnswers where AuditID=@AuditID
      )
  
  fetch next from AuditsCursor
  into @AuditID
  end
  
  close AuditsCursor
  deallocate AuditsCursor
  </code></pre>