Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    20

    Unanswered: INSERT INTO -action with a constraint...??? Help?

    Hi.

    I have a table which has columns for:
    Date Job_ID Person_ID Val_1 Val_2 Val_3 Val_4




    At this moment I have only a few rows, but I want to insert new ones. The only restriction is that the user could never insert new rows that have the same Date, Job_ID and Person_ID as existing ones...

    (Lets say I already have a row with values "2010-01-01, 10, 10, 1, 2, 3, 4". Now the user want's to insert a new row with the same values, but the program should first check if that row already exists...)
    I tried the following code:
    Code:
    IF (SELECT COUNT (*) FROM Table
    WHERE Date='2010-01-01' AND Job_ID='10' AND Person_ID='10') = '0' THEN
    INSERT INTO Table
    VALUES ('2010-01-01', '10', '10', '0', '0', '0', '0')

    The idea behind the above code is that SQL should check if a row with the specific values already exists. If not, (COUNT (*) = '0'), then the query executes the INSERT command...

    This does not work in SQL Server. I get an error. A bit of help?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Hamsori View Post
    I get an error. A bit of help?
    i can't see the error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2010
    Posts
    20
    Quote Originally Posted by r937 View Post
    i can't see the error message
    The error message is:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'THEN'.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    THEN isn't a Transact-SQL keyword, remove it.

    It looks like you've gone "Quote Happy" in your code. Strings and dates need to be quoted in Transact-SQL, but other data types don't need quotes and really shouldn't have them even though Transact SQL tolerates extraneous quotes pretty well. Count returns a BIGINT, not a string so it should be compared against 0, not against '0'.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha, now i see it

    from BOL --
    Code:
    Syntax
    
    IF Boolean_expression
       { sql_statement| statement_block } 
    [ ELSE 
       { sql_statement| statement_block }
    can you see the error now?
    Last edited by r937; 01-12-11 at 11:29.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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