Inserting Data with Raw SQL
The SQL command INSERT inserts data into a database. An alternative is to use the
Mathematica command
SQLInsert, as described in
"Inserting Data".
If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the
DatabaseExamples` package, as described in
"Using the Example Databases".
When inserting data, the result of
SQLExecute is an integer specifying the number of rows affected by the query.
Here is an example that inserts data. This loads
DatabaseLink and connects to the
demo database.
As discussed in
"Creating Tables with Raw SQL", the
ADDRESSES and
MAILER tables should be created.
This demonstrates an SQL statement that inserts a row into the
ADDRESSES table.
| Out[5]= |  |
A SELECT statement verifies that the data has been added to the table.
| Out[6]= |  |
The
USERNAME column is made to be a primary key, which means that it must be unique. If you try to insert the same data again, there is an error and the result is
$Failed.
| Out[7]= |  |
With this command, the
USERNAME parameter is unique, but
ADDRESS,
CITY, and
ZIPCODE are not. These must also be unique and again there is an error.
| Out[8]= |  |
This inserts unique values of
ADDRESS,
CITY, and
ZIPCODE.
| Out[9]= |  |
A SELECT statement verifies that the data has been added to the table.
| Out[10]= |  |
A prepared statement may be more useful for working with data to insert. In addition,
SQLArgument may be useful to reduce the number of argument fields in the prepared statement.
SQLArgument is described in
Argument Sequences in SQL-Style Queries.
| Out[11]= |  |
A SELECT statement verifies that the data has been added to the table.
| Out[12]= |  |
Identity columns are very useful as they automatically increment their values and do not require a value. They are also the primary key for the table, which means they uniquely identify a row. Identity values should be set to
Null in a SQL statement.
| Out[13]= |  |
| Out[14]= |  |
A SELECT statement verifies that the data has been added to the table.
| Out[15]= |  |
Since
USERNAME is a foreign key, its value must be present in
ADDRESSES. The following fails because
user4 is not present in
ADDRESSES.
| Out[16]= |  |
The
SENDMAILER column has a default value and is therefore not required when data is inserted.
| Out[17]= |  |
A SELECT statement verifies that the data exists in the database and ties the values together.
Out[18]//TableForm= |
| |  |
This deletes the tables and closes the connection.
