title: How to Insert/Update if record not exist / exists with SQL Server 2008?
body: <p>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 <code>if not exists</code> 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 <code>email</code> or <code>username</code>do not exist in the table. Here is example of my query:</p>
  
  <pre><code>&lt;cfset var isUser = structKeyExists(FORM, "frmSaveaccount_isuser") ? true : false&gt;
  &lt;cfset var isStaff = structKeyExists(FORM, "frmSaveaccount_isstaff") ? true : false&gt;
  
  &lt;cftransaction action="begin"&gt;
      &lt;cftry&gt;
           &lt;cfquery name="saveAccount" datasource="#Application.dsn#"&gt;
              DECLARE @AccountID UNIQUEIDENTIFIER = CASE WHEN LEN('#FORM.frm_accountid#') &lt;&gt; 0 THEN &lt;cfqueryparam cfsqltype="cf_sql_idstamp" value="#trim(FORM.frm_accountid)#"&gt; ELSE NEWID() END;
              DECLARE @FirstName VARCHAR(50) = &lt;cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_firstname)#"&gt;;
              DECLARE @LastName VARCHAR(50) = &lt;cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_lastname)#"&gt;;
              DECLARE @Middle CHAR(1) = &lt;cfqueryparam cfsqltype="cf_sql_char" maxlength="1" value="#FORM.frm_middle#" null="#!len(trim(FORM.frmSaveaccount_middle))#"&gt;;
              DECLARE @Email VARCHAR(80) = &lt;cfqueryparam cfsqltype="cf_sql_varchar" maxlength="80" value="#trim(FORM.frm_email)#"&gt;;
              &lt;cfif isUser&gt;
                  DECLARE @IsUser BIT = &lt;cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_isuser')? 1:0)#"&gt;;
                  DECLARE @ActiveUser BIT = &lt;cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_activeuser')? 1:0)#"&gt;;
                  DECLARE @SystemAdmin BIT = &lt;cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_systemadmin')? 1:0)#"&gt;;
                  DECLARE @UserName VARCHAR(50) = &lt;cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(FORM.frm_username)#"&gt;;
              &lt;/cfif&gt;
              &lt;cfif isStaff&gt;
                  DECLARE @IsStaff BIT = &lt;cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_isstaff')? 1:0)#"&gt;;
                  DECLARE @ActiveStaff BIT = &lt;cfqueryparam cfsqltype="cf_sql_bit" maxlength="1" value="#trim(structKeyExists(FORM, 'frm_activestaff')? 1:0)#"&gt;;
                  DECLARE @Position VARCHAR(10) = &lt;cfqueryparam cfsqltype="cf_sql_varchar" maxlength="10" value="#trim(FORM.frm_positioncode)#" null="#!len(trim(FORM.frm_positioncode))#"&gt;;
              &lt;/cfif&gt;
              DECLARE @ActionDate DATETIME = CURRENT_TIMESTAMP;
              DECLARE @ActionID UNIQUEIDENTIFIER = &lt;cfqueryparam cfsqltype="cf_sql_idstamp" value="#AccountID#"&gt;;
  
              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,
                          &lt;cfif isUser&gt;
                              IsUser = @IsUser,
                              ActiveUser = @ActiveUser,
                              SystemAdmin = @SystemAdmin,
                              UserName = @UserName,
                          &lt;/cfif&gt;
                          &lt;cfif isStaff&gt;
                              IsStaff = @IsStaff,
                              ActiveStaff = @ActiveStaff,
                              Position = @Position,
                          &lt;/cfif&gt;
                          ActionDate = @ActionDate,
                          ActionID = @ActionID
                      WHERE AccountID = @AccountID
                      SELECT @AccountID AS RecID
                  END
                  ELSE
                  BEGIN
                      IF NOT EXISTS(SELECT 1 FROM Accounts WHERE Email = @Email &lt;cfif isUser&gt; OR UserName = @UserName&lt;/cfif&gt;)
                          INSERT INTO Accounts (
                              AccountID,FirstName,LastName,Middle,Email,
                          &lt;cfif isUser&gt;
                              IsUser,ActiveUser,SystemAdmin,UserName,
                          &lt;/cfif&gt;
                          &lt;cfif isStaff&gt;
                              IsStaff,ActiveStaff,Position,
                          &lt;/cfif&gt;
                              ActionDate,ActionID
                          ) VALUES (
                              @AccountID,@FirstName,@LastName,@Middle,@Email,
                          &lt;cfif isUser&gt;
                              @IsUser,@ActiveUser,@SystemAdmin,@UserName,
                          &lt;/cfif&gt;
                          &lt;cfif isStaff&gt;
                              @IsStaff,@ActiveStaff,@Position,
                          &lt;/cfif&gt;
                              @ActionDate,@ActionID
                          )
                          SELECT @AccountID AS RecID
                      END
                      COMMIT TRAN
                  &lt;/cfquery&gt;
                  &lt;cfcatch type="any"&gt;
                       &lt;cftransaction action="rollback" /&gt;
                       &lt;cfset var fnResults.status = "400"&gt;
                       &lt;cfset var fnResults.message = "Error! Please contact your administrator."&gt;
                  &lt;/cfcatch&gt;
               &lt;/cftry&gt;
      &lt;/cftransaction&gt;
  </code></pre>
  
  <p>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?</p>