SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE

by

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.

More Related Contents:

  1. Insert local .csv data in remote SQL Server via already working connection string
  2. Syntax combine two stored procedure into one [closed]
  3. How to delete duplicate rows in SQL Server?
  4. CROSS JOIN vs INNER JOIN in SQL
  5. How can I schedule a job to run a SQL query daily?
  6. Reset identity seed after deleting records in SQL Server
  7. How to avoid the “divide by zero” error in SQL?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. SQL Server 2008: how do I grant privileges to a username?
  11. Generate script in SQL Server Management Studio
  12. Split values over multiple rows [duplicate]
  13. How to do an inner join on row number in sql server
  14. How can I alter this computed column in SQL Server 2008?
  15. How to get the size of a varchar[n] field in one SQL statement?
  16. SQL Server: should I use information_schema tables over sys tables?
  17. TSQL – How to use GO inside of a BEGIN .. END block?
  18. SQL take just the numeric values from a varchar
  19. SQL Server SELECT INTO @variable?
  20. How do I change db schema to dbo
  21. How to subtract 30 days from the current date using SQL Server
  22. Count records for every month in a year
  23. How to export data from Excel spreadsheet to Sql Server 2008 table
  24. SQL Server: +(unary) operator on non-numeric Strings
  25. Use one CTE many times
  26. Bulk insert using stored procedure
  27. Connect different Windows User in SQL Server Management Studio (2005 or later)
  28. How to Select Every Row Where Column Value is NOT Distinct
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment