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

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

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.

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.

Few other suggestions about the CF code

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

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

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>

Answer 2

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.

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 3

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