SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE
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.
Solution 1:
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.
Solution 2:
As others have suggested that you should look into MERGE statement but nobody provided a solution using it I'm adding my own answer with this particular TSQL construct. I bet you'll like it.
Important note
Your code has a typo in your if statement in not exists(select...) part. Inner select statement has only one where condition while UserName condition is excluded from the not exists due to invalid brace completion. In any case you cave too many closing braces.
I assume this based on the fact that you're using two where conditions in update statement later on in your code.
Let's continue to my answer...
SQL Server 2008+ support MERGE statement
MERGE statement is a beautiful TSQL gem very well suited for "insert or update" situations. In your case it would look similar to the following code. Take into consideration that I'm declaring variables what are likely stored procedure parameters (I suspect).
declare @clockDate date = '08/10/2012';
declare @userName = 'test';
merge Clock as target
using (select @clockDate, @userName) as source (ClockDate, UserName)
on (target.ClockDate = source.ClockDate and target.UserName = source.UserName)
when matched then
update
set BreakOut = getdate()
when not matched then
insert (ClockDate, UserName, BreakOut)
values (getdate(), source.UserName, getdate());
Solution 3:
IF NOT EXISTS(SELECT * FROM Clock
WHERE clockDate = '08/10/2012') AND userName = 'test')
Has an extra parenthesis. I think it's fine if you remove it:
IF NOT EXISTS(SELECT * FROM Clock WHERE
clockDate = '08/10/2012' AND userName = 'test')
Also, GETDATE() will put the current date in the column, though if you don't want the time you'll have to play a little. I think CONVERT(varchar(8), GETDATE(), 112) would give you just the date (not time) portion.
IF NOT EXISTS(SELECT * FROM Clock WHERE
clockDate = CONVERT(varchar(8), GETDATE(), 112)
AND userName = 'test')
should probably do it.
PS: use a merge statement :)
Solution 4:
You need to replace it as WHERE clockDate = { fn CURRENT_DATE() } AND userName = 'test'.
Please remove extra ")" from { fn CURRENT_DATE() })