SQL Insert into two tables

I am using the following SQL statement to insert new project iterations to the Iterations table...I can insert many iterations at once. The Iteration开发者_StackOverflows table has the folllowing fields: {ProjectIteratationID(PK), ProjectID, StartDate, EndDate}

INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES (...)

What I also want to do is assign people to the iterations that I am adding, so I need to insert into the ProjectIterationMember table.

"INSERT INTO ProjectIterationMember (ProjectIterationID, MemberID) VALUES ((SELECT ProjectIterationID AS pro_it_id FROM Iterations WHERE ProjectID = '" + proj_id + "'), @member_id)";

I am getting an error. My nested select statement retrieves more than one result.

For example, if I am adding two iterations, the PK 13 and 14 will be generated. I then want to copy the PK to the ProjectIterationMember table and assign a few MemberIDs to those iteration IDs. Thanks for your help!

Use:

INSERT INTO ProjectIterationMember 
  (ProjectIterationID, MemberID) 
SELECT ProjectIterationID AS pro_it_id, @member_id
  FROM Iterations 
 WHERE ProjectID = '" + proj_id + "'

SQL allows you to provide statically assigned values in the SELECT clause.

If you use insert in combination with select you have to omit the values keyword. Simply use the select after the column list.

Use SCOPE_IDENTITY to get last identity value.

DECLARE @iterationID int;

INSERT INTO Iterations (ProjectID, StartDate, EndDate)
VALUES (@projectID, @startDate, @endDate);

SET @iterationID = SCOPE_IDENTITY();

INSERT INTO ProjectIterationMember (ProjectIterationID, MemberID) 
VALUES (@iterationID, @memberID);