INSERT Execution

When an INSERT statement is executed, the cmdlet executes the POST method of the schema, where you can build the HTTP insert request.

Execute Inserts to XML

In the POST method, the _input item, one of the Items in API Script, contains the columns to insert. For example, consider the following statement:

INSERT INTO Persons
(Name, Id)
VALUES
('Maria Anders','7')

You can use the _input item's attributes to set these column values in the HTTP insert request. In the OData API, this is an HTTP POST. The POST data to make the preceding insert in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:Name m:type="Edm.String">Maria Anders</d:Name>
      <d:ID m:type="Edm.Int32">7</d:ID>
    </m:properties>
  </content>
</entry>

In the example schema's corresponding POST method, the required columns are first validated and then used to define the POST data. You can check that a column was provided and alert the user otherwise with the api:validate keyword.

After validating that the needed attributes exist, you can set the column values in the POST data. Set the "data" attribute to the POST data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:validate attr="_input.Name" desc="Name and Id are required to insert." />
    <api:validate attr="_input.Id" desc="Name and Id are required to insert." />
    <api:set attr="data">
      <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
        <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
        <content type="application/xml">
          <m:properties>
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
            <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          </m:properties>
        </content>
      </entry>
    </api:set>
    <api:call op="xmlproviderGet"/>
  </api:script>

Note that an INSERT statement can sometimes return data, for example, the generated Id of the new record. If you want to access values returned from an insert, use the api:push keyword instead of the api:call keyword.

Access Components of INSERT Statements

In the POST method, the _query item contains the following attributes:


query	The SQL statement, as in the following statement: INSERT INTO Account (account_name, account_type) VALUES ('Contoso','Company')
table	The table in the SQL statement. For example, Account in the preceding query.

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

Cmdlets for XML

INSERT Execution

Execute Inserts to XML

Access Components of INSERT Statements

Keyword Reference