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

UPSERT Syntax

The UPSERT syntax is the same as for INSERT. NetSuite 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 insert the record must be specified. See Data Model for any table-specific information.

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

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

C#

String connectionString = "AccountId=XABC123456;Schema=SuiteTalk;AuthScheme=Token;OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessToken=MyOAuthAccessToken;OAuthAccessTokenSecret=MyOAuthAccessTokenSecret;";
int rowsAffected;
using (NetSuiteConnection connection = new NetSuiteConnection(connectionString)) {
  NetSuiteCommand cmd = new NetSuiteCommand("UPSERT INTO Account (AcctName) VALUES ('Petty Cash')", connection);
  rowsAffected = cmd.ExecuteNonQuery();
}

VB.NET

Dim connectionString As [String] = "AccountId=XABC123456;Schema=SuiteTalk;AuthScheme=Token;OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessToken=MyOAuthAccessToken;OAuthAccessTokenSecret=MyOAuthAccessTokenSecret;"
Dim rowsAffected As [Integer]
Using connection As New NetSuiteConnection(connectionString)
  Dim cmd As New NetSuiteCommand("UPSERT INTO Account (AcctName) VALUES ('Petty Cash')", connection)
  rowsAffected = cmd.ExecuteNonQuery()
End Using

Retrieving Generated Ids

To retrieve the InternalId of the last inserted 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 InternalId = (String)returnedValues;

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