SQL insert statement

Question

I want to do the following:

I have a table called Name which has an ID field.

I have another blank table called Transcript

I want to take the ID#s from Name and insert them into Transcript where they do not exist.

Secondly I want to create 10 records with a different COURSE# value in the Transcript table.

Therefore for each Name.ID I would like 10 records in Transcript.ID with a different value under course # ie; 101,201,301

Answer 1

Something like this might work:

INSERT INTO TableB

SELECT TableA.id FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

Answer 2

Another query that will return the same thing

INSERT INTO TABLEB SELECT TableA.ID FROm TableA WHERE ID NOT IN (SELECT Id FROM TableB)

Answer 3

Assuming that you are getting your course numbers from some outside courses table, here is the t-sql:

INSERT INTO transcript (name_id, course_id)
SELECT n.name_id, c.course_id
FROM name n 
CROSS APPLY courses c
WHERE n.last_name = 'xxx'

This will insert all of the courses in the table for all of the names found by the where clause.