SQL Server 2008 - 如果不存在，则插入其他更新 [英] SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

问题描述

我很抱歉，但这是一个由两部分组成的问题.

I apologize, but this is kind of a two part question.

我对 SQL 非常陌生，正在尝试为我工作的小型办公室开发一个时钟应用程序.我现在正在使用 SQL 后端，并有一个关于复合语句的问题.

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.

我遇到的问题是，如果用户尝试打卡休息但从未在轮班开始时打卡，则 SQL 需要创建一个新行而不是更新现有行.

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.

这是我尝试过的:

    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

我使用 Visual Studio 2010 执行此操作，连接到本地计算机上的 SQL Server Express 2008.我收到一条错误消息，指出不支持复合语句 SQL 构造或语句."但是，紧随其后的是一条消息，指出 1 行已受到影响，当我查看我的时钟表时，它看起来就像我期望的那样.实现这一目标的最佳方法是什么?

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?

这个问题的第二部分在我的 WHERE 语句中.是否有一个函数可以在 clockDate 列中获取今天的日期，而不必填充今天的日期?只是想提前考虑构建前端应用程序.

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')

同样，这给了我想要的结果，但直到收到错误'CURRENT_DATE'附近的 WHERE 子句错误.无法解析查询文本."

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!!

@RThomas@w00te

@RThomas @w00te

好的，那么将 clockDate 作为日期字段并将 breakOut 作为 time(0) 字段，这是否可行?因为我仍然收到不支持复合语句 SQL 构造或语句".语法错误，即使它似乎有效.

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

我的表格结果是:

clockDate  userName  clockIn  breakOut  breakIn  clockOut

08/10/2012  test      NULL    11:24:38   NULL     NULL

这是我想要的结果，但这个错误让我很困惑.这是 Visual Studio 错误还是 SQL 错误?我会仔细阅读 Merge Statements，谢谢你们提供的链接.

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.

推荐答案

乍一看，您最初的尝试似乎非常接近.我假设clockDate是一个DateTime字段，所以试试这个:

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 

请注意 getdate 为您提供当前日期.如果您尝试与日期(没有时间)进行比较，则需要强制转换，否则时间元素将导致比较失败.

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.

如果 clockDate 不是 datetime 字段(只是日期)，那么 SQL 引擎会为您执行此操作 - 无需在 set/insert 语句上进行转换.

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 

正如其他人所指出的，merge 语句是解决相同逻辑的另一种方法.但是，在某些情况下，尤其是对于大型数据集，merge 语句可能会非常慢，从而导致大量 tran 日志活动.因此，知道如何将其逻辑化，如上所示仍然是一种有效的技术.

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.

这篇关于SQL Server 2008 - 如果不存在，则插入其他更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！