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.

SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

4 Answers