INSERT

INSERT is used to insert rows into a given list using any of the INSERT ... VALUES or INSERT ... SET forms to specify column-value pairs. The INSERT ... VALUES form allows multiple inserts in one statement at better performance than multiple single statements.

Syntax

INSERT INTO
list_name [.{content_type_name | ATTACHMENTS}]
(col_name [, col_name ...])
VALUES (expr [, expr ...]) [, (...) ...];

OR

INSERT INTO
list_name [.{content_type_name | ATTACHMENTS}]
SET col_name = expr [, col_name = expr ...];

The INSERT statement is used as follows:

  1. The list_name indicates the list into which the row should be inserted. Optionally, you can also specify the content type of the new row. If content type is not specified, the row is inserted with default content type.

    Example: to insert a new row of default content type into list Colors:

    INSERT INTO Colors (Title, HexCode) VALUES ('Red', '#FF0000')

    Example: to insert three new rows of default content type into list Colors:

    INSERT INTO Colors (Title, HexCode) VALUES ('Red', '#FF0000'), ('Green', '#00FF00'), ('Blue', '#0000FF')

    Example: to insert a new row of content type Greyish into list Colors:

    INSERT INTO Colors.Greyish (Title, HexCode) VALUES ('Light', '#B0B0B0')

  2. If you choose to specify a comma-separated list of column names following the list name, a value for each named column must be listed in the VALUES clause.
  3. Use the SET clause to provide value for each column explicitly.

    Example: to insert a new row into list Colors using the SET form:

    INSERT INTO Colors SET Title = 'Red', HexCode = '#FF0000'

  4. In StrictMode, SPC will generate an error if trying to insert value into a read-only column (for example the ID column of a list) or if value of a required column is missing.
  5. In StrictMode, SPC will generate an error if trying to insert a string value that exceeds the column's maximum length.
  6. In StrictMode, SPC will generate an error if trying to insert a numeric value that lies outside the column's range or precision.

    Note: When StrictMode is turned off, no range or precision validation is performed before data is sent to SP, possibly allowing values outside the boundaries. Therefore it is recommended to keep this option on.

  7. Any column not explicitly given a value is set to its default value. Use the DEFAULT keyword to set a column explicitly to its default value. This makes it easier to build INSERT statements that sets some but not all columns.

    Example: using DEFAULT keyword to set a column explicitly to its default value:

    INSERT INTO Colors SET Title = 'Red', HexCode = '#FF0000', Enabled = DEFAULT

  8. When the procedure returns, the application can obtain the ID of the last inserted item through the LastInsertedId property of the SharePointCommand class.

Code examples

Create new customer

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"INSERT INTO Customers SET Title = 'King Carl XVI Gustaf', Country = 'Sweden'", connection))
    {
        command.ExecuteNonQuery();
        Console.WriteLine("ID = " + command.LastInsertedId);
    }
}

Code result

ID = 14

Create customer log

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"INSERT INTO Log (Title, Customer) VALUES ('Logged in', 12)", connection))
    {
        command.ExecuteNonQuery();
        Console.WriteLine("ID = " + command.LastInsertedId);
    }
}

Code result

ID = 5

Create customer log using parameters

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"INSERT INTO Log (Title, Customer) VALUES (@p1, @p2)", connection))
    {
        command.Parameters.Add("@p1", "Edited 'Phone number'");
        command.Parameters.Add("@p2", 12);
        command.ExecuteNonQuery();
        Console.WriteLine("ID = " + command.LastInsertedId);
    }
}

Code result

ID = 6

Note: It is recommended to use parameters because this protects against sql injection attacks and ensures that special characters can be inserted without escaping.


Skip Navigation Links.

Visa     MasterCard