# Inserting data

Adding data to a database involves executing a SQL `INSERT` statement. Once the
statement has finished executing, you can access the primary key for the newly
inserted row if the key was generated by the database.

## Executing an INSERT statement

To add data to a table in a database, you create and execute a
[SQLStatement](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLStatement.html)
instance whose text is a SQL `INSERT` statement.

The following example uses a SQLStatement instance to add a row of data to the
already-existing employees table. This example demonstrates inserting data using
asynchronous execution mode. Note that this listing assumes that there is a
[SQLConnection](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLConnection.html)
instance named `conn` that has already been instantiated and is already
connected to a database. It also assumes that the "employees" table has already
been created.

    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;

    // ... create and open the SQLConnection instance named conn ...

    // create the SQL statement
    var insertStmt:SQLStatement = new SQLStatement();
    insertStmt.sqlConnection = conn;

    // define the SQL text
    var sql:String =
    	"INSERT INTO employees (firstName, lastName, salary) " +
    	"VALUES ('Bob', 'Smith', 8000)";
    insertStmt.text = sql;

    // register listeners for the result and failure (status) events
    insertStmt.addEventListener(SQLEvent.RESULT, insertResult);
    insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError);

    // execute the statement
    insertStmt.execute();

    function insertResult(event:SQLEvent):void
    {
    	trace("INSERT statement succeeded");
    }

    function insertError(event:SQLErrorEvent):void
    {
    	trace("Error message:", event.error.message);
    	trace("Details:", event.error.details);
    }

    <?xml version="1.0" encoding="utf-8"?>
    <mx:WindowedApplication xmlns:mx="https://www.adobe.com/2006/mxml" creationComplete="init()">
    	<mx:Script>
    		<![CDATA[
    			import flash.data.SQLConnection;
    			import flash.data.SQLResult;
    			import flash.data.SQLStatement;
    			import flash.events.SQLErrorEvent;
    			import flash.events.SQLEvent;

    			private function init():void
    			{
    				// ... create and open the SQLConnection instance named conn ...

    				// create the SQL statement
    				var insertStmt:SQLStatement = new SQLStatement();
    				insertStmt.sqlConnection = conn;

    				// define the SQL text
    				var sql:String =
    					"INSERT INTO employees (firstName, lastName, salary) " +
    					"VALUES ('Bob', 'Smith', 8000)";
    				insertStmt.text = sql;

    				// register listeners for the result and failure (status) events
    				insertStmt.addEventListener(SQLEvent.RESULT, insertResult);
    				insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError);

    				// execute the statement
    				insertStmt.execute();
    			}

    			private function insertResult(event:SQLEvent):void
    			{
    				trace("INSERT statement succeeded");
    			}

    			private function insertError(event:SQLErrorEvent):void
    			{
    				trace("Error message:", event.error.message);
    				trace("Details:", event.error.details);
    			}
    		]]>
    	</mx:Script>
    </mx:WindowedApplication>

The following example adds a row of data to the already-existing employees
table, using synchronous execution mode. Note that this listing assumes that
there is a
[SQLConnection](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLConnection.html)
instance named `conn` that has already been instantiated and is already
connected to a database. It also assumes that the "employees" table has already
been created.

    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.errors.SQLError;

    // ... create and open the SQLConnection instance named conn ...

    // create the SQL statement
    var insertStmt:SQLStatement = new SQLStatement();
    insertStmt.sqlConnection = conn;

    // define the SQL text
    var sql:String =
    	"INSERT INTO employees (firstName, lastName, salary) " +
    	"VALUES ('Bob', 'Smith', 8000)";
    insertStmt.text = sql;

    try
    {
    	// execute the statement
    	insertStmt.execute();

    	trace("INSERT statement succeeded");
    }
    catch (error:SQLError)
    {
    	trace("Error message:", error.message);
    	trace("Details:", error.details);
    }

    <?xml version="1.0" encoding="utf-8"?>
    <mx:WindowedApplication xmlns:mx="https://www.adobe.com/2006/mxml" creationComplete="init()">
    	<mx:Script>
    		<![CDATA[
    			import flash.data.SQLConnection;
    			import flash.data.SQLResult;
    			import flash.data.SQLStatement;
    			import flash.errors.SQLError;

    			private function init():void
    			{
    				// ... create and open the SQLConnection instance named conn ...

    				// create the SQL statement
    				var insertStmt:SQLStatement = new SQLStatement();
    				insertStmt.sqlConnection = conn;

    				// define the SQL text
    				var sql:String =
    					"INSERT INTO employees (firstName, lastName, salary) " +
    					"VALUES ('Bob', 'Smith', 8000)";
    				insertStmt.text = sql;

    				try
    				{
    					// execute the statement
    					insertStmt.execute();
    					trace("INSERT statement succeeded");
    				}
    				catch (error:SQLError)
    				{
    					trace("Error message:", error.message);
    					trace("Details:", error.details);
    				}
    			}
    		]]>
    	</mx:Script>
    </mx:WindowedApplication>

## Retrieving a database-generated primary key of an inserted row

Often after inserting a row of data into a table, your code needs to know a
database-generated primary key or row identifier value for the newly inserted
row. For example, once you insert a row in one table, you might want to add rows
in a related table. In that case you would want to insert the primary key value
as a foreign key in the related table. The primary key of a newly inserted row
can be retrieved using the
[SQLResult](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLResult.html)
object associated with the statement execution. This is the same object that's
used to access result data after a `SELECT` statement is executed. As with any
SQL statement, when the execution of an `INSERT` statement completes the runtime
creates a SQLResult instance. You access the SQLResult instance by calling the
[SQLStatement](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLStatement.html)
object's `getResult()` method if you're using an event listener or if you're
using synchronous execution mode. Alternatively, if you're using asynchronous
execution mode and you pass a
[Responder](https://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/net/Responder.html)
instance to the `execute()` call, the SQLResult instance is passed as an
argument to the result handler function. In any case, the SQLResult instance has
a property, `lastInsertRowID`, that contains the row identifier of the
most-recently inserted row if the executed SQL statement is an `INSERT`
statement.

The following example demonstrates accessing the primary key of an inserted row
in asynchronous execution mode:

    insertStmt.text = "INSERT INTO ...";

    insertStmt.addEventListener(SQLEvent.RESULT, resultHandler);

    insertStmt.execute();

    function resultHandler(event:SQLEvent):void
    {
    	// get the primary key
    	var result:SQLResult = insertStmt.getResult();

    	var primaryKey:Number = result.lastInsertRowID;
    	// do something with the primary key
    }

The following example demonstrates accessing the primary key of an inserted row
in synchronous execution mode:

    insertStmt.text = "INSERT INTO ...";

    try
    {
    	insertStmt.execute();

    	// get the primary key
    	var result:SQLResult = insertStmt.getResult();

    	var primaryKey:Number = result.lastInsertRowID;
    	// do something with the primary key
    }
    catch (error:SQLError)
    {
    /	/ respond to the error
    }

Note that the row identifier may or may not be the value of the column that is
designated as the primary key column in the table definition, according to the
following rules:

- If the table is defined with a primary key column whose affinity (column data
  type) is `INTEGER`, the `lastInsertRowID` property contains the value that was
  inserted into that row (or the value generated by the runtime if it's an
  `AUTOINCREMENT` column).

- If the table is defined with multiple primary key columns (a composite key) or
  with a single primary key column whose affinity is not `INTEGER`, behind the
  scenes the database generates an integer row identifier value for the row.
  That generated value is the value of the `lastInsertRowID` property.

- The value is always the row identifier of the most-recently inserted row. If
  an `INSERT` statement causes a trigger to fire which in turn inserts a row,
  the `lastInsertRowID` property contains the row identifier of the last row
  inserted by the trigger rather than the row created by the `INSERT` statement.

As a consequence of these rules, if you want to have an explicitly defined
primary key column whose value is available after an `INSERT` command through
the `SQLResult.lastInsertRowID` property, the column must be defined as an
`INTEGER PRIMARY KEY` column. Even if your table does not include an explicit
`INTEGER PRIMARY KEY` column, it is equally acceptable to use the
database-generated row identifier as a primary key for your table in the sense
of defining relationships with related tables. The row identifier column value
is available in any SQL statement by using one of the special column names
`ROWID`, `_ROWID_`, or `OID`. You can create a foreign key column in a related
table and use the row identifier value as the foreign key column value just as
you would with an explicitly declared `INTEGER PRIMARY KEY` column. In that
sense, if you are using an arbitrary primary key rather than a natural key, and
as long as you don't mind the runtime generating the primary key value for you,
it makes little difference whether you use an `INTEGER PRIMARY KEY` column or
the system-generated row identifier as a table's primary key for defining a
foreign key relationship with between two tables.

For more information about primary keys and generated row identifiers, see
[SQL support in local databases](../../appendixes/sql-support-in-local-databases/index.md).
