SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

Question

I apologize  but this is kind of a two part question   I m extremely new to SQL and am trying to develop a time clock application for the small office that I work in  I m playing around with the SQL backend right now and have a question about compound statements   Where I m stuck is if a user tries to clock out for break but never clocked in at the start of the shift  SQL needs to create a new row rather than update an existing   Here is what I tried       IF NOT EXISTS SELECT   FROM Clock WHERE clockDate    08 10 2012   AND userName    test       BEGIN         INSERT INTO Clock clockDate  userName  breakOut          VALUES   fn NOW       test     fn NOW          END     ELSE     BEGIN         UPDATE Clock         SET breakOut     fn NOW             WHERE  clockDate    08 10 2012   AND  userName    test       END   I m using Visual Studio 2010 to do this connected to SQL Server Express 2008 on my local machine  I get an error that says  The Compound statement SQL construct or statement is not supported   However  that is followed by a message that 1 row has been affected  and when I view my Clock table it looks just like what I expect it to look like  What is the best way to acclompish this   And my second part of this question is in my WHERE statements  Is there a function to get today s date in the clockDate column rather than have to populate today s date  Just trying to think ahead for building the front end application       IF NOT EXISTS SELECT   FROM Clock WHERE clockDate     fn CURRENT DATE      AND userName    test     Again  this gives me the results I want  but not until after getting an error  Error in WHERE clause near  CURRENT DATE   Unable to parse query text    I hope I have explained this properly  and thank you for your help    EDIT    RThomas  w00te  OK  so with the clockDate as a date field and breakOut as a time 0  field  should this work  Cause I m still getting a  The Compound statement SQL construct or statement is not supported   Syntax error even though it seems to be working       IF NOT EXISTS  SELECT   FROM Clock WHERE  clockDate   GETDATE    AND  userName    test        BEGIN         INSERT INTO Clock clockDate  userName  breakOut          Values GETDATE     test   GETDATE        END     ELSE     BEGIN         UPDATE Clock         SET breakOut   GETDATE           WHERE  clockDate   GETDATE    AND  userName    test       END   My table results are   clockDate  userName  clockIn  breakOut  breakIn  clockOut  08 10 2012  test      NULL    11 24 38   NULL     NULL   This is the result I want but this error confuses me  Is this a Visual Studio error or a SQL error  And I ll read up on Merge Statements  thank you both for the links

User · Answer

IF NOT EXISTS SELECT   FROM Clock WHERE clockDate    08 10 2012   AND userName    test     Has an extra parenthesis   I think it s fine if you remove it   IF NOT EXISTS SELECT   FROM Clock WHERE clockDate    08 10 2012  AND userName    test     Also  GETDATE   will put the current date in the column  though if you don t want the time you ll have to play a little   I think CONVERT varchar 8   GETDATE    112  would give you just the date  not time  portion   IF NOT EXISTS SELECT   FROM Clock WHERE clockDate   CONVERT varchar 8   GETDATE    112  AND userName    test     should probably do it   PS  use a merge statement

User · Answer

At first glance your original attempt seems pretty close   I m assuming that clockDate is a DateTime fields so try this   IF  NOT EXISTS SELECT   FROM Clock WHERE cast clockDate as date     08 10 2012        AND userName    test    BEGIN      INSERT INTO Clock clockDate  userName  breakOut       VALUES GetDate     test   GetDate     END  ELSE  BEGIN      UPDATE Clock      SET breakOut   GetDate       WHERE Cast clockDate AS Date     08 10 2012  AND userName    test  END    Note that getdate gives you the current date   If you are trying to compare to a date  without the time  you need to cast or the time element will cause the compare to fail     If clockDate is NOT datetime field  just date   then the SQL engine will do it for you - no need to cast on a set insert statement   IF  NOT EXISTS SELECT   FROM Clock WHERE clockDate    08 10 2012        AND userName    test    BEGIN      INSERT INTO Clock clockDate  userName  breakOut       VALUES GetDate     test   GetDate     END  ELSE  BEGIN      UPDATE Clock      SET breakOut   GetDate       WHERE clockDate    08 10 2012  AND userName    test  END    As others have pointed out  the merge statement is another way to tackle this same logic   However  in some cases  especially with large data sets  the merge statement can be prohibitively slow  causing a lot of tran log activity   So knowing how to logic it out as shown above is still a valid technique

User · Answer

You need to replace it as WHERE clockDate     fn CURRENT DATE     AND userName    test   Please remove extra      from   fn CURRENT DATE

User · Answer

As others have suggested that you should look into MERGE statement but nobody provided a solution using it I m adding my own answer with this particular TSQL construct  I bet you ll like it   Important note Your code has a typo in your if statement in not exists select     part  Inner select statement has only one where condition while UserName condition is excluded from the not exists due to invalid brace completion  In any case you cave too many closing braces  I assume this based on the fact that you re using two where conditions in update statement later on in your code  Let s continue to my answer    SQL Server 2008  support MERGE statement MERGE statement is a beautiful TSQL gem very well suited for  quot insert or update quot  situations  In your case it would look similar to the following code  Take into consideration that I m declaring variables what are likely stored procedure parameters  I suspect   declare  clockDate date    08 10 2012   declare  userName    test    merge Clock as target using  select  clockDate   userName  as source  ClockDate  UserName  on  target ClockDate   source ClockDate and target UserName   source UserName  when matched then     update     set BreakOut   getdate   when not matched then     insert  ClockDate  UserName  BreakOut      values  getdate    source UserName  getdate

[sql] SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

Examples related to sql

Examples related to sql-server-2008