Reputation: 21

SQL insert statement

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

Upvotes: 2

Answers (3)

Reputation: 4475

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.

Upvotes: 0

Reputation: 6430

Another query that will return the same thing

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

Upvotes: 0

Reputation: 85036

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

Upvotes: 1