CODEWITHSUNDEEP
c#sql
MiziaQ
MiziaQ

Reputation: 271

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 Iterations 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!

Upvotes: 0

Views: 614

Answers (3)

Anthony Faull
Anthony Faull

Reputation: 17957

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);

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332531

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.

Upvotes: 3

Dennis Benzinger
Dennis Benzinger

Reputation: 783

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

Upvotes: 2

Related Questions