Insert or Update Stored Procedure

• Veronica Loria

  SSC Enthusiast

  Points: 195

  More actions

  December 3, 2007 at 4:35 am

  #62751

  Hi Everyone,

  I have 2 tables. New_Candidates and Candidates. I'm trying to insert any new records of "New_Candidates" table into "Candidates", and if the [Candidate ID] (key) already exists, I would like the record to be updated instead.

  Does someone have some similar stored procedure code to help me do this ? (just basically a stored procedure to insert or update the records of one table to another). Any help is greatly appreciated.

  Thanks in Advance,

  Veronica,

  message_4_u2001@yahoo.com

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  December 3, 2007 at 5:17 am

  #756413

  EDIT:

  Is this what you're looking for....

  --Method 1

  IF EXISTS( SELECT * FROM dbo.New_Candidates WHERE CandidateID = @iCandidateID )

  BEGIN

  UPDATECan

  SET Can.CandidateName = NewCan.CandidateName

  FROMdbo.Candidates Can

  INNER JOIN dbo.New_Candidates NewCan ON Can.CandidateID = NewCan.CandidateID

  WHERECan.CandidateID= @iCandidateID

  END

  ELSE

  BEGIN

  INSERTdbo.Candidates( CandidateID, CandidateName )

  SELECTCandidateID, CandidateName

  FROMdbo.New_Candidates

  WHERECandidateID= @iCandidateID

  END

  --Method 2

  UPDATECan

  SETCan.CandidateName = NewCan.CandidateName

  FROMdbo.Candidates Can

  INNER JOIN dbo.New_Candidates NewCan ON Can.CandidateID = NewCan.CandidateID

  WHERECan.CandidateID= @iCandidateID

  IF ( @@ROWCOUNT = 0 )

  BEGIN

  INSERTdbo.Candidates( CandidateID, CandidateName )

  SELECTCandidateID, CandidateName

  FROMdbo.New_Candidates

  WHERECandidateID= @iCandidateID

  END

  --Ramesh
  

  ---

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  December 3, 2007 at 5:44 am

  #756426

  Or to do all the candidates in one go, rather than one at a time.

  -- update where matched

  UPDATE Candidates Set Candidates.Column1 = New_Candidates.Column1, ....

  FROM New_Candidates

  WHERE Candidates.Candidate_ID = New_Candidates.Candidate_ID

  -- Insert where not

  INSERT INTO Candidates (Column1, ...)

  SELECT Column1, ...

  FROM New_Candidates

  WHERE Candidate_ID NOT IN (SELECT Candidate_ID FROM Candidates)

  Hope that helps.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Veronica Loria

  SSC Enthusiast

  Points: 195

  More actions

  December 3, 2007 at 6:04 am

  #756433

  Thank you so much to all your responses !.

  I'll give them a try and see how it goes. Thanks again.