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, 2022 in Database by Kithuzzz
• 38,000 points
4,199 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, 2022 in Database by Kithuzzz
• 38,000 points
2,147 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, 2022 in Database by Kithuzzz
• 38,000 points
827 views
0 votes
1 answer

SQL Server: IF EXISTS ; ELSE

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

answered Sep 19, 2022 in Database by narikkadan
• 63,640 points
3,843 views
0 votes
0 answers

Check if table exists in SQL Server

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

Aug 21, 2022 in Database by Kithuzzz
• 38,000 points
38,017 views
0 votes
0 answers

If else in stored procedure sql server

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

Aug 22, 2022 in Database by Kithuzzz
• 38,000 points
958 views
0 votes
0 answers

Solutions for INSERT OR UPDATE on SQL Server

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

Aug 27, 2022 in Database by Kithuzzz
• 38,000 points
751 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, 2022 in Others by Soham
• 9,710 points
1,119 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, 2022 in Database by Vaani
• 7,070 points
663 views
0 votes
0 answers

How to update Identity Column in SQL Server?

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

Aug 9, 2022 in Database by Kithuzzz
• 38,000 points
1,345 views
0 votes
0 answers

Query to check index on a table

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

Aug 11, 2022 in Database by Kithuzzz
• 38,000 points
500 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