SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

0 votes

I'm trying to create a time clock application for the little office where I work, but I'm quite new to SQL. Currently experimenting with the SQL backend, I have a compound statement query. Where I get stuck is that SQL needs to build a new row rather than update an existing one if a user wants to clock out for a break but never clocked in at the start of the shift.

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 working with SQL Server Express 2008 on my own machine using Visual Studio 2010. The Compound Statement SQL construct or statement is not supported, according to the error message I receive. But then I get a notification saying that 1 row has been impacted, and when I inspect my Clock table, it exactly matches my expectations. What is the most effective technique to complete this?

And I've answered the second portion of this query in my WHERE expressions. Is there a way to retrieve the current date in the clockDate column without having to manually fill it in? merely making an effort to plan ahead for developing 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.". Can someone please help me with this?

Aug 28 in Database by Kithuzzz
• 12,240 points
41 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

SQL Server Insert if not exists

I want to add data to my ...READ MORE

Aug 25 in Database by Kithuzzz
• 12,240 points
9 views
0 votes
0 answers

Check if table exists and if it doesn't exist, create it in SQL Server 2008

I'm using SQL Server 2008 to create ...READ MORE

Sep 2 in Database by Kithuzzz
• 12,240 points
28 views
0 votes
1 answer

SQL Server: IF EXISTS ; ELSE

Try this: update b set code = ...READ MORE

answered Sep 19 in Database by narikkadan
• 20,880 points
24 views
0 votes
0 answers

Check if table exists in SQL Server

I want this to be the last ...READ MORE

Aug 21 in Database by Kithuzzz
• 12,240 points
33 views
0 votes
0 answers

If else in stored procedure sql server

I have created a stored procedure as ...READ MORE

Aug 22 in Database by Kithuzzz
• 12,240 points
26 views
0 votes
0 answers

Solutions for INSERT OR UPDATE on SQL Server

Consider the MyTable table structure. I frequently ...READ MORE

Aug 27 in Database by Kithuzzz
• 12,240 points
16 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10 in Others by Soham
• 9,330 points
196 views
0 votes
1 answer

Rename column SQL Server 2008

Use sp_rename EXEC sp_RENAME 'TableName.PreviousColumnName' , 'NewColumnName', 'COLUMN' See: SQL SERVER ...READ MORE

answered Feb 23 in Database by Vaani
• 7,020 points
53 views
0 votes
0 answers

How to update Identity Column in SQL Server?

With 200 records currently, I want to ...READ MORE

Aug 9 in Database by Kithuzzz
• 12,240 points
174 views
0 votes
0 answers

Query to check index on a table

I want a query to see if ...READ MORE

Aug 11 in Database by Kithuzzz
• 12,240 points
32 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP