如果记录在SQL Server 2008中不存在/存在，如何插入/更新?

I'm wondering what would be the best way to check if record exists and then run update statement or check if record not exists and then run Insert statement? Reason why I would need if not exists is because I'm inserting Account information in the table. In situation when we have to update I'm just looking for unique ID. In other case for the Insert I have to make sure that email or usernamedo not exist in the table. Here is example of my query:

我想知道，检查记录是否存在，然后运行更新语句或检查记录是否不存在，然后运行插入语句的最佳方法是什么?如果不存在，我之所以需要，是因为我在表中插入了帐户信息。当我们需要更新时，我只是在寻找唯一的ID，在其他情况下，我必须确保电子邮件或usernamedo不存在于表中。下面是我的查询示例:

<cfset var isUser = structKeyExists(FORM, "frmSaveaccount_isuser") ? true : false>
<cfset var isStaff = structKeyExists(FORM, "frmSaveaccount_isstaff") ? true : false>

<cftransaction action="begin">
    <cftry>
         <cfquery name="saveAccount" datasource="#Application.dsn#">
            DECLARE @AccountID UNIQUEIDENTIFIER = CASE WHEN LEN('#FORM.frm_accountid#') <> 0 THEN <cfqueryparam cfsqltype="cf_sql_idstamp" value="#trim(FORM.frm_accountid)#"> ELSE NEWID() END;
            DECLARE @FirstName VARCHAR(50) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_firstname)#">;
            DECLARE @LastName VARCHAR(50) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_lastname)#">;
            DECLARE @Middle CHAR(1) = <cfqueryparam cfsqltype="cf_sql_char" maxlength="1" value="#FORM.frm_middle#" null="#!len(trim(FORM.frmSaveaccount_middle))#">;
            DECLARE @Email VARCHAR(80) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="80" value="#trim(FORM.frm_email)#">;
            <cfif isUser>
                DECLARE @IsUser BIT = <cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_isuser')? 1:0)#">;
                DECLARE @ActiveUser BIT = <cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_activeuser')? 1:0)#">;
                DECLARE @SystemAdmin BIT = <cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_systemadmin')? 1:0)#">;
                DECLARE @UserName VARCHAR(50) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_username)#">;
            </cfif>
            <cfif isStaff>
                DECLARE @IsStaff BIT = <cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_isstaff')? 1:0)#">;
                DECLARE @ActiveStaff BIT = <cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_activestaff')? 1:0)#">;
                DECLARE @Position VARCHAR(10) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="10" value="#trim(FORM.frm_positioncode)#" null="#!len(trim(FORM.frm_positioncode))#">;
            </cfif>
            DECLARE @ActionDate DATETIME = CURRENT_TIMESTAMP;
            DECLARE @ActionID UNIQUEIDENTIFIER = <cfqueryparam cfsqltype="cf_sql_idstamp" value="#AccountID#">;

            BEGIN TRAN
                IF EXISTS (SELECT AccountID FROM Accounts WITH (updlock,serializable) WHERE AccountID = @AccountID)
                BEGIN
                    UPDATE Accounts 
                    SET
                        FirstName = @FirstName,
                        LastName = @LastName,
                        Middle = @Middle,
                        Email = @Email,
                        <cfif isUser>
                            IsUser = @IsUser,
                            ActiveUser = @ActiveUser,
                            SystemAdmin = @SystemAdmin,
                            UserName = @UserName,
                        </cfif>
                        <cfif isStaff>
                            IsStaff = @IsStaff,
                            ActiveStaff = @ActiveStaff,
                            Position = @Position,
                        </cfif>
                        ActionDate = @ActionDate,
                        ActionID = @ActionID
                    WHERE AccountID = @AccountID
                    SELECT @AccountID AS RecID
                END
                ELSE
                BEGIN
                    IF NOT EXISTS(SELECT 1 FROM Accounts WHERE Email = @Email <cfif isUser> OR UserName = @UserName</cfif>)
                        INSERT INTO Accounts (
                            AccountID,FirstName,LastName,Middle,Email,
                        <cfif isUser>
                            IsUser,ActiveUser,SystemAdmin,UserName,
                        </cfif>
                        <cfif isStaff>
                            IsStaff,ActiveStaff,Position,
                        </cfif>
                            ActionDate,ActionID
                        ) VALUES (
                            @AccountID,@FirstName,@LastName,@Middle,@Email,
                        <cfif isUser>
                            @IsUser,@ActiveUser,@SystemAdmin,@UserName,
                        </cfif>
                        <cfif isStaff>
                            @IsStaff,@ActiveStaff,@Position,
                        </cfif>
                            @ActionDate,@ActionID
                        )
                        SELECT @AccountID AS RecID
                    END
                    COMMIT TRAN
                </cfquery>
                <cfcatch type="any">
                     <cftransaction action="rollback" />
                     <cfset var fnResults.status = "400">
                     <cfset var fnResults.message = "Error! Please contact your administrator.">
                </cfcatch>
             </cftry>
    </cftransaction>

I'm wondering if this is better approach then split in two separate queries insert/update? Also is there better way to check if record exists/not exists?

我想知道这是不是更好的方法，然后在两个单独的查询插入/更新中分离?还有更好的方法来检查记录是否存在/不存在吗?

3 个解决方案

#1

The MERGE may be something along the lines of

合并可能是沿着这条线。

MERGE Accounts tgt
USING ( SELECT 
            AccountID = 42 
          , firstName = 'Ted'
          , userName = 'ted'
          , email = 'ted@logan.com'
) src (AccountID, firstName, userName, email)
  ON tgt.accountID = src.accountID
WHEN MATCHED 
THEN 
  UPDATE
  SET FirstName = src.firstName
WHEN NOT MATCHED
  /* Check if username or email is already used */
  AND (SELECT 1 FROM Accounts WHERE username = src.username OR email = src.email) IS NULL
THEN 
  INSERT ( accountID, firstName, email, userName )
  VALUES ( src.AccountID, src.firstName, src.email, src.username )
OUTPUT $action, inserted.AccountID
;

As I said above, I'm not sure if cfquery can properly interpret a MERGE statement. You'll have to test that. You may have to make it a stored procedure call.

如上所述，我不确定cfquery是否能够正确地解释MERGE语句。你得测试一下。您可能需要将它设置为一个存储过程调用。

OUTPUT should return the type of operations it was (INSERT or UPDATE) and the AccountID associated.

输出应该返回它所在的操作类型(插入或更新)和与之关联的AccountID。

EDIT: I created a Fiddle to demonstrate some of the different uses you're attempting here.

编辑:我创建了一个小提琴演示你在这里尝试的一些不同的用途。

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=710ea9d801637c17c88f27cac165a8f5

Although, to be honest, the more I thought about this, the more I thought that MERGE was more intended for bulk data upserts. The above method works, but it's a single row. It may be more efficient to just test for the existence of the requested record and then INSERT or UPDATE as needed. A MERGE may be overkill.

虽然，老实说，我越想这个，我就越觉得MERGE更适合于大量数据更新者。上面的方法可以工作，但是它是一行。只测试请求记录的存在性，然后根据需要插入或更新，可能会更有效。合并可能会过度。

#2

Prior to 2008 the approach you are using is pretty much there. There's no "Upsert" in SQL Server that will take care of it for you so you must check yourself.

在2008年之前，您所使用的方法已经基本实现了。在SQL Server中没有“Upsert”，因此您必须检查自己。

Before 2008 - Generic

在2008年之前——通用

IF EXISTS(SELECT [PrimaryKey] FROM [MyTable] WHERE [PrimaryKey] = @PassedInID)
    BEGIN
        UPDATE [MyTable]
        SET [Field1] = @PassedInValue1,
            [Field2] = @PassedInValue2
        WHERE [PrimaryKey] = @PassedInID
    END
ELSE
    BEGIN
        INSERT INTO [MyTable] ([PrimaryKey], [Field1], [Field2])
        VALUES (@PassedInID, @PassedInValue1, @PassedInValue2)
    END

If you are making a lot of updates and calling this many times, either pass in the primary key, or index the value you are passing in. This will save SQL Server from loading the table data to know whether an insert update is needed.

如果您正在进行多次更新并多次调用，要么传入主键，要么将传入的值进行索引。这将节省SQL Server加载表数据，以了解是否需要插入更新。

However, if you are calling it many times, it would be better to pass in a table of all inserts/updates and JOIN on the existing table twice, and just perform one INSERT and one UPDATE

但是，如果您多次调用它，那么最好将包含所有插入/更新的表传递给现有表，并在现有表上连接两次，然后只执行一次插入和一次更新

2008 & Later - Sepcific With 2008 and later you can use MERGE (Thanks for @Shawn for pointing out it was that old)

2008年晚些时候- 2008年9月和之后你可以使用MERGE(感谢@Shawn指出它有那么老)

MERGE INTO [Accounts] AS target
USING (SELECT @AccountID) AS source ([AccountID])  
    ON (target.[Email] = source.Email AND target.[Username] = @Username)  
    WHEN MATCHED THEN  
        UPDATE SET [FirstName] = @FirstName
                , [LastName] = @LastName
                , [Middle] = @Middle
                , [Email] = @Email
                , [Username] = @Username
WHEN NOT MATCHED THEN  
    INSERT ([AccountID], [FirstName], [LastName], [Middle], [Email], [Username])  
    VALUES (@AccountID, @FirstName, @LastName, @Middle, @Email, @Username)

All-in-one If you have to check the email and username as the same time, you could mix IF NOT EXISTS and MERGE

一体机，如果你必须同时检查电子邮件和用户名，你可以混合如果不存在和合并

IF NOT EXISTS(SELECT [PrimaryKey] FROM [MyTable] WHERE [Email] = @Email OR [Username] = @Username)
    BEGIN
        MERGE INTO [Accounts] AS target
        USING (SELECT @AccountID) AS source ([AccountID])  
            ON (target.[Email] = source.Email AND target.[Username] = @Username)  
            WHEN MATCHED THEN  
                UPDATE SET [FirstName] = @FirstName
                        , [LastName] = @LastName
                        , [Middle] = @Middle
                        , [Email] = @Email
                        , [Username] = @Username
        WHEN NOT MATCHED THEN  
            INSERT ([AccountID], [FirstName], [LastName], [Middle], [Email], [Username])  
            VALUES (@AccountID, @FirstName, @LastName, @Middle, @Email, @Username)
    END

#3

(Too long for comments ...)

(评论太长了…)

The other answers already answered the primary question. This post is to answer to your earlier question about how a stored procedure could streamline things.

其他的答案已经回答了主要的问题。这篇文章将回答您先前关于存储过程如何简化事物的问题。

Although using a cfquery is valid, personally I'd use a stored procedure instead. Procedures are better at handling complex sql and would also make it easier to take advantage of sql NULL's and/or defaults to simplify the logic.

虽然使用cfquery是有效的，但是我个人会使用存储过程。过程可以更好地处理复杂的sql，并且可以更容易地利用sql NULL和/或默认值来简化逻辑。

Instead of having a bunch of cfif/cfelse statements strewn throughout the SQL, create a stored procedure with all of the required variables. Assign whatever default values you want for the optional parameters. NULL is usually a good choice, because it makes it easy to detect omitted parameters and substitute different values with ISNULL() or COALESCE(), but .. all depends on your app's business logic.

不要在SQL中到处都是委员会/cfelse语句，而是创建一个包含所有必需变量的存储过程。为可选参数分配您想要的任何默认值。NULL通常是一个很好的选择，因为它可以很容易地检测被忽略的参数，并用ISNULL()或COALESCE()替换不同的值，但是…这一切都取决于你的应用程序的业务逻辑。

Stored Procedure Signature

存储过程签名

CREATE PROCEDURE [dbo].[YourProcedureNameHere]
    @AccountID UNIQUEIDENTIFIER
    , @FirstName VARCHAR(50)
    , @LastName VARCHAR(50)
    , @Middle CHAR(1)
    , @Email VARCHAR(80)
    , @IsUser BIT = NULL
    , @ActiveUser BIT = NULL
    , @SystemAdmin BIT = NULL
    , @UserName VARCHAR(50) = NULL 
    , @IsStaff BIT = NULL
    , @ActiveStaff BIT = NULL
    , @Position VARCHAR(10) = NULL 
AS 
BEGIN
      ... your sql logic ....
END
GO

Then call the procedure from CF, using a single cfif to conditionally pass in the appropriate variables for either a "user" or "staff". Whichever set of variables are omitted (user or staff settings), will be assigned default values inside the stored procedure.

然后从CF调用该过程，使用一个该中心f有条件地为“用户”或“人员”传递适当的变量。无论哪一组变量被省略(用户或人员设置)，将在存储过程中指定默认值。

Few other suggestions about the CF code

关于CF代码的其他建议很少

Like @Shawn said, most of the ternary operators aren't needed. Functions like structKeyExists() already return a boolean value. No need to do anything extra like trim(), etc.. to use the result with a CF_SQL_BIT column. It's converted automagically.

就像@Shawn说的，大多数三元运算符是不需要的。像structKeyExists()这样的函数已经返回一个布尔值。不需要做任何额外的事情，如修剪()等。要使用CF_SQL_BIT列的结果。它是自动转换。
Transactions are for multiple statements. Since the UPDATE and INSERT are atomic, the transaction statement don't really do anything. Least not with default transaction level.

事务用于多个语句。由于更新和插入是原子的，事务语句实际上什么都不做。至少没有默认事务级别。
Since the code appears to be contained within a cffunction, don't use the form scope directly. Whatever values the function needs should be declared and passed into function using the arguments scope.

由于代码似乎包含在cffunction中，所以不要直接使用表单范围。应该声明函数所需的任何值，并使用arguments范围将其传递给函数。

There's probably additional ways to streamline things but this should give you a good start.

可能还有其他的方法来简化事情，但是这应该会给你一个好的开始。

Sample Procedure Call

过程调用示例

<cfstoredproc procedure="YourProcedureNameHere" datasource="#yourDSN#">
    <cfprocparam type="in" dbvarname="@AccountID" cfsqltype="cf_sql_char" value="#arguments.frm_accountid#" ....>
    ... more params ...
    <cfprocparam type="in" dbvarname="@Email" cfsqltype="cf_sql_varchar" value="#arguments.frm_email#">

    <!--- User --->
    <cfif structKeyExists(ARGUMENTS, "frmSaveaccount_isuser")>
        <cfprocparam type="in" dbvarname="@IsUser" cfsqltype="cf_sql_bit" value="1">
        <cfprocparam type="in" dbvarname="@ActiveUser" cfsqltype="cf_sql_bit" value="#structKeyExists(arguments, 'frm_activeuser')#">
        ... more params ... 
    </cfif>

    <!--- Staff --->
    <cfif structKeyExists(ARGUMENTS, "frmSaveaccount_isstaff")>
        <cfprocparam type="in" dbvarname="@IsStaff" cfsqltype="cf_sql_bit" value="1">
        <cfprocparam type="in" dbvarname="@ActiveStaff" cfsqltype="cf_sql_bit" value="#structKeyExists(arguments, 'frm_activeuser')#">
        ... more params ... 
    </cfif>


</cfstoredproc>

#1