Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    28

    Unanswered: don't insert if record exists

    Code:
    /*if key values exist don't insert new record*/
    SELECT
    
    /*if exists don't insert*/
    CASE 
    WHEN ISNULL(gradeId, -1) = -1 THEN 
    INSERT INTO tblScores 
    (gtStudentId, assignmentId, score) 
    VALUES (@nStudent, @nAssignment, 0) 
    END 
    
    FROM tblScores
    WHERE gtStudentId = @nStudent AND assignmentId = @nAssignment
    tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

    I'm getting syntax errors when I click check syntax (near keywords insert from and end).

    one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

    thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I think the problem is that the INSERT-statement is not a result_expression (see BOL). Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?

  3. #3
    Join Date
    May 2004
    Posts
    28
    Quote Originally Posted by Kaiowas
    Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?
    Its rather complicated but I'll try:

    This code is inside a trigger for an enrollment table. If a student joins a class late (after assignements have been assigned) then the trigger creates new records in the scores table giving the new student a 0 for each assignment already assigned to the class. If a student gets unenrolled from the class the scores are maintained in case the student gets reenrolled. If the student gets reenrolled then I want to do a select on the scores table and bypass any scores the student may have had prior to being unenrolled.
    Quote Originally Posted by Kaiowas
    INSERT-statement is not a result_expression
    How does one do a conditional INSERT?

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    IF NOT EXISTS(
    SELECT TOP 1 gradeId
    FROM tblScores
    WHERE
    gtStudentId = @nStudent
    AND assignmentId = @nAssignment)
    BEGIN

    INSERT INTO tblScores (
    gtStudentId,
    assignmentId,
    score)

    SELECT
    @nStudent,
    @nAssignment,
    0
    END
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I forget which site...but we just did this...Gotta find the code I wrote...

    Why would you want to double every access to the database?

    Just handle the dup key error....in a calling sproc or the application...

    EDIT: It was originally about Contraints....same thing

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 char(1), CHECK(Col1 IN ('Y','N')))
    GO
    
    CREATE PROC mySproc99 @x char(1) AS INSERT INTO myTable99 SELECT @x RETURN
    GO
    
    CREATE PROC mySproc00 
    AS 
      DECLARE @rc int 
      EXEC @rc = mySproc99 'Y'
      SELECT @rc, @@ERROR
      EXEC @rc = mySproc99 'B'
      SELECT @rc, @@ERROR
    GO
    
    EXEC mySproc00
    GO
    
    DROP PROC mySproc00
    DROP PROC mySproc99
    DROP TABLE myTable99
    GO
    Last edited by Brett Kaiser; 06-21-04 at 16:59.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    I did not understand very clearly what you are trying to do... is it inserting into your table only those records that _do not already exist_ in that table?

    If so, ty this:

    Insert into tblScores (gtStudentId, assignmentId, score)

    VALUES (@nStudent, @nAssignment, 0)

    Where (not exists (Select * from tblScores
    Where WHERE gtStudentId = @nStudent
    AND assignmentId = @nAssignment))

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My Point, just attempt the insert...and trap the Error if it fails

    Don't make 2 data access attempts to the database
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •