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.
Needs["DatabaseLink`"];
conn = OpenSQLConnection["demo"];As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.
SQLExecute[conn,
"CREATE TABLE ADDRESSES (
USERNAME VARCHAR(64) NOT NULL PRIMARY KEY,
ADDRESS VARCHAR(64),
CITY VARCHAR(64),
ZIPCODE VARCHAR(16),
UNIQUE (ADDRESS, CITY, ZIPCODE))"];
SQLExecute[conn, "CREATE TABLE MAILER (
MAILERID INT IDENTITY,
USERNAME VARCHAR(64) NOT NULL,
SENDMAILER BIT DEFAULT '1' NOT NULL,
FOREIGN KEY (USERNAME) REFERENCES ADDRESSES (USERNAME))"];This demonstrates an SQL statement that inserts a row into the ADDRESSES table.
SQLExecute[conn,
"INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES
('user1', '100 Trade Center', 'Champaign, IL', '61820')"]A SELECT statement verifies that the data has been added to the table.
SQLExecute[conn, "SELECT * FROM ADDRESSES"]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.
SQLExecute[conn,
"INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES
('user1', '100 Trade Center', 'Champaign, IL', '61820')"]
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.
SQLExecute[conn,
"INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES
('user2', '100 Trade Center', 'Champaign, IL', '61820')"]
This inserts unique values of ADDRESS, CITY, and ZIPCODE.
SQLExecute[conn,
"INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES
('user2', '200 Trade Center', 'Champaign, IL', '61820')"]A SELECT statement verifies that the data has been added to the table.
SQLExecute[conn, "SELECT * FROM ADDRESSES"]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".
SQLExecute[conn, "INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES
(`1`)", {SQLArgument["user3", "300 Trade Center", "Champaign, IL", "61820"]}]A SELECT statement verifies that the data has been added to the table.
SQLExecute[conn, "SELECT * FROM ADDRESSES"]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.
SQLColumnNames[conn, "MAILER"]SQLExecute[conn, "INSERT INTO MAILER (MAILERID, USERNAME, SENDMAILER) VALUES (NULL, 'user1', 0)"]A SELECT statement verifies that the data has been added to the table.
SQLExecute[conn, "SELECT * FROM MAILER"]Since USERNAME is a foreign key, its value must be present in ADDRESSES. The following fails because user4 is not present in ADDRESSES.
SQLExecute[conn, "INSERT INTO MAILER (MAILERID, USERNAME, SENDMAILER) VALUES (NULL, 'user4', 0)"]
The SENDMAILER column has a default value and is therefore not required when data is inserted.
SQLExecute[conn, "INSERT INTO MAILER (MAILERID, USERNAME) VALUES (NULL, 'user2')"]A SELECT statement verifies that the data exists in the database and ties the values together.
SQLExecute[conn, "SELECT USERNAME, ADDRESS, CITY, ZIPCODE, SENDMAILER FROM ADDRESSES, MAILER WHERE ADDRESSES.USERNAME = MAILER.USERNAME", "ShowColumnHeadings" -> True]//TableFormThis deletes the tables and closes the connection.
SQLExecute[conn, "DROP TABLE MAILER"];
SQLExecute[conn, "DROP TABLE ADDRESSES"];
CloseSQLConnection[conn];