INSERT INTO and SELECT statement

[JohnnyLong]

Hi there,

We have a SQL7 table where each record holds 5 CandidateID's (CandidateID1 to CandidateID5). We now need to split Candidate2, 3, 4 and 5 so they have their own record.

I need to insert a record into tblEvents, return the Identity and insert the record into tblCVDespatch using the Identity as the key.

The following would be for CandidateID2:

Declare @pIDENTITY int

/**** Insert the CV event into the events table ****/
INSERT INTO tblEvents(EventType, Subject, ClientID, CandidateID, OrderID, ContactID, CompletedFlag, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)

SELECT 'CV Sent', Subject, ClientID, CandidateID2, OrderID, ContactID, CompletedFlag, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy FROM tblCVDespatch
WHERE CandidateID2 <>0

/****Get the IDENTITY of the last insert ****/
SELECT @pIDENTITY = (SELECT @@IDENTITY)

INSERT INTO tblCVDespatch(CVID,CandidateID2, ClientID, ContactID, OrderID, IPSJobNumber, CandidateFirstName, CandidateSurname, CompanyName, ContactFirstName,
ContactSurname, ContactKnownAs, ContactJobTitle, ContactEmail, Subject,JobDescription, Comments, CandidateAddressLine1, CandidateAddressLine2, CandidateAddressTown,
CandidateAddressCounty, CandidateAddressCountry, CandidateAddressPostcode, ClientAddressLine1, ClientAddressLine2, ClientAddressTown,
ClientAddressCounty, ClientAddressCountry, ClientAddressPostcode, CVDocID, CVDate, FullPathName, CompletedFlag, CompletedReason, CreatedBy,
CreatedDate, ModifiedBy, ModifiedDate, InUseBy)

SELECT @pIDENTITY, CandidateID2, ClientID, ContactID, OrderID, IPSJobNumber, CandidateFirstName, CandidateSurname, CompanyName, ContactFirstName,
ContactSurname, ContactKnownAs, ContactJobTitle, ContactEmail, Subject,JobDescription, Comments, CandidateAddressLine1, CandidateAddressLine2, CandidateAddressTown,
CandidateAddressCounty, CandidateAddressCountry, CandidateAddressPostcode, ClientAddressLine1, ClientAddressLine2, ClientAddressTown,
ClientAddressCounty, ClientAddressCountry, ClientAddressPostcode, CVDocID, CVDate, FullPathName, CompletedFlag, CompletedReason, CreatedBy,
CreatedDate, ModifiedBy, ModifiedDate, InUseBy
FROM tblCVDespatch
WHERE ( candidateID2 <>0)


How do I do this so the correct record is copied into tblCVDespatch?

Thanks,

John