Inserting Data with Raw SQL
The SQL command INSERT inserts data into a database. An alternative is to use the Wolfram Language 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 "DatabaseLink`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.
A SELECT statement verifies that the data has been added to the table.
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.
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.
This inserts unique values of ADDRESS, CITY, and ZIPCODE.
A SELECT statement verifies that the data has been added to the table.
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 "SQL Execute: Argument Sequences in SQL-Style Queries".
A SELECT statement verifies that the data has been added to the table.
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 an SQL statement.
A SELECT statement verifies that the data has been added to the table.
Since USERNAME is a foreign key, its value must be present in ADDRESSES. The following fails because user4 is not present in ADDRESSES.
The SENDMAILER column has a default value and is therefore not required when data is inserted.
A SELECT statement verifies that the data exists in the database and ties the values together.