SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE
sql, sql-server-2008
Solution
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.
Problem
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.