title: SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE
body: <p>I apologize, but this is kind of a two part question.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is what I tried:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <pre><code>    IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = { fn CURRENT_DATE() }) AND userName = 'test')
  </code></pre>
  
  <p>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."</p>
  
  <p>I hope I have explained this properly, and thank you for your help!!</p>
  
  <p>EDIT:</p>
  
  <p>@RThomas
  @w00te</p>
  
  <p>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.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>My table results are:</p>
  
  <pre><code>clockDate  userName  clockIn  breakOut  breakIn  clockOut
  
  08/10/2012  test      NULL    11:24:38   NULL     NULL
  </code></pre>
  
  <p>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.</p>