CODEWITHSUNDEEP
t-sqlsql-server-2005
StackTrace
StackTrace

Reputation: 9416

How to use WHILE EXISTS in a loop

CREATE TABLE [CandidateDocsAssociation](
[Row_ID] [bigint] IDENTITY(1,1) NOT NULL,
[Doc_ID] [bigint] NOT NULL,
[Candidate_ID] [bigint] NOT NULL,
) ON [PRIMARY]
GO

I have the above table structure to store the association between documents and candidates. Row_ID is an auto generated primary key. Doc_ID is a foreign key referencing the documents table. Candidate_ID is also a foreign key referencing the Candidates table.

A candidate can be associated with more than one document and one document can be associated with multiple candidates.

What i want to achieve is insert a default common document (Doc_ID) for all candidates(DISTINCT) if a Candidate_ID row with a DOC_ID of 2 does not already exist.

Below is what i'm trying but it ain't working

WHILE EXISTS (SELECT DISTINCT Candidate_ID from CandidateDocsAssociation
  WHERE Doc_ID <> (SELECT   Doc_ID FROM Doc_Table WHERE Doc_Name = N'Default'))
  BEGIN
  INSERT CandidateDocsAssociation (Doc_ID, Candidate_ID) VALUES  ((SELECT Doc_ID FROM Doc_Table WHERE Doc_Name = N'Default'),Candidate_ID)
  END
  GO

Upvotes: 0

Views: 6866

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

Forget the loop and do a set-based operation. Assuming you have a Candidates table:

  INSERT INTO CandidateDocsAssociation (Doc_ID, Candidate_ID) 
  SELECT dt.Doc_ID, c.Candidate_ID 
  FROM Doc_Table dt
  CROSS JOIN Candidates c 
  WHERE dt.Doc_Name = N'Default'
  AND NOT EXISTS(SELECT * FROM CandidateDocsAssociation cda 
    WHERE cda.Candidate_ID=c.Candidate_ID
    AND cda.Doc_ID=dt.Doc_ID)

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1541

try with this (use NOT IN Clause)

WHILE EXISTS (SELECT DISTINCT Candidate_ID from CandidateDocsAssociation
  WHERE Doc_ID NOT IN (SELECT   Doc_ID FROM Doc_Table WHERE Doc_Name = N'Default'))
  BEGIN
  INSERT CandidateDocsAssociation (Doc_ID, Candidate_ID) VALUES  ((SELECT Doc_ID FROM Doc_Table WHERE Doc_Name = N'Default'),Candidate_ID)
  END
  GO

Upvotes: 0

Related Questions