How to Insert/Update if record not exist / exists with SQL Server 2008?

Question

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:

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

Answer 1

The MERGE may be something along the lines of

MERGE Accounts tgt
USING ( SELECT 
            AccountID = 42 
          , firstName = 'Ted'
          , userName = 'ted'
          , email = '[email protected]'
) 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.

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

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.

Answer 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.

Before 2008 - Generic

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.

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)

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