An UPSERT statement updates an existing record or creates a new record if an existing record is not identified.

Configuring UPSERT-s

UPSERT-s can only be performed on a column which is explicitly defined to serve as the record identifier. You need to provide the name of this column in the ExternalIdColumn column, as shown in the following query:

UPSERT INTO Contacts (`First name`, `Email`, `Upsert Property`, `ExternalIdColumn`) 
VALUES ('John', '[email protected]', '31d06273-de69-4150-9ece-764d267222f6', 'Upsert Property')
VALUES ('Jane', '[email protected]', 'f80ccbab-598e-4e44-8f9b-95d40182e196', 'Upsert Property')

The identifier column must not be read-only and its values must be unique to each record in the table/HubSpot object. You can read the IsReadOnly and HasUniqueValues columns from sys_tablecolumns to find out the eligible UPSERT identifier columns. Refer to the query below:

SELECT * FROM sys_tablecolumns WHERE TableName = 'Contacts' AND IsReadOnly = false AND HasUniqueValues = true 

UPSERT syntax

The UPSERT syntax is the same as the syntax for the INSERT statement:

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  
<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

HubSpot uses the input provided in the VALUES clause to determine whether the record already exists. If the record does not exist, all columns required to create the record must be specified. See データモデル for any table-specific information.

You can use the ExecuteNonQuery method to execute data manipulation commands and retrieve the rows affected, as shown in the following example:

C#

String connectionString = "InitiateOAuth=GETANDREFRESH;";
int rowsAffected;
using (HubSpotConnection connection = new HubSpotConnection(connectionString)) {
  HubSpotCommand cmd = new HubSpotCommand("UPSERT INTO Contacts (`First name`, `Email`, `Upsert Property`, `ExternalIdColumn`) VALUES ('John', '[email protected]', '31d06273-de69-4150-9ece-764d267222f6', 'Upsert Property')", connection);
  rowsAffected = cmd.ExecuteNonQuery();
}

VB.NET

Dim connectionString As [String] = "InitiateOAuth=GETANDREFRESH;"
Dim rowsAffected As [Integer]
Using connection As New HubSpotConnection(connectionString)
  Dim cmd As New HubSpotCommand("UPSERT INTO Contacts (`First name`, `Email`, `Upsert Property`, `ExternalIdColumn`) VALUES ('John', '[email protected]', '31d06273-de69-4150-9ece-764d267222f6', 'Upsert Property')", connection)
  rowsAffected = cmd.ExecuteNonQuery()
End Using

Retrieving Generated Keys

To retrieve the generated key columns of the last UPSERT-ed record, use the SCOPE_IDENTITY function, as shown in the following example:

C#

cmd = connection.CreateCommand();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Object returnedValues = cmd.ExecuteScalar();
String Id = (String) returnedValues;

VB.NET

cmd = connection.CreateCommand()
cmd.CommandText = "SELECT SCOPE_IDENTITY()"
Dim returnedValues As [Object] = cmd.ExecuteScalar()
Dim Id As [String] = returnedValues