In all the previous articles I’ve written on SQL I’ve showed you how to get data out of the database.  Now, we’re switching gears.  I’m going to show you how to put data into the database.  The command to put data into the database is INSERT.  There are four main ways to use this command, so let’s dive right in.

Grab the example file here, so you can follow along on your own database server.

If you wanted to put a record into the productSale table, you have to understand what columns it has, and what types of data it wants for each column.  So let’s take a look at the table definition for productSale.

CREATE TABLE productSale (
buyer VARCHAR(255)
, productName VARCHAR(255)
, purchaseDate DATETIME
, qtypurchased INT
, pricePaid DECIMAL(9,2)
)

Based on this script, you can see the different data types each column expect.  In case you are unfamiliar with the data types, search this site for articles on “data types” and you’ll see a lot more detail than I’ll go into here. A varchar is a variable length string.  Datetimes are dates, int is an integer, and decimal(9,2) is a decimal.  Given this definition we know that the following INSERT statement will work.

INSERT INTO productSale
VALUES('Shannon Lowder', 'paper', '1/1/2000', 2, 1.00)

There is a slightly different version of this statement that lets you use SELECT instead of VALUES.  I prefer this version, since you can only use VALUES with one record of data at a time.  If you use the SELECT version, you can also use UNION or UNION ALL and insert multiple rows at once.

INSERT INTO productSale
SELECT 'Shannon Lowder', 'paper', '1/1/2000', 2, 1.00

Either way, both of the previous statements would insert the same data into the table.  Both show you the bare minimum required to insert a record into a table.  INSERT into [table name], and list the values you want to put into each column.  As long as you list a value for each column, and the data type for each value is compatible with the one the table is expecting, you’re golden.  If you have too many or too few columns, or if any one column has an incompatible type for the column that value is going into… you’ll receive an error from your server telling so.  Also, if you don’t include a value for a column that has been marked required, you’d get an error for that to.

But what if you don’t want to give a value for a certain column.  What if that column is optional, and you don’t want to enter it?  Then you’ll have to tell the interpreter which columns you are passing.  To do so, you have to alter your command slightly

INSERT INTO productSale
(buyer, productName, qtyPurchased, pricePaid)
SELECT 'Shannon Lowder', 'paper', 2, 1.00

Now you can enter just the information you want into the table.  This leaves us with one last method for getting data into a table.  What if we want to take data from one table, and insert it into another table.  We can take the last statement we wrote and alter is again to accomplish this task.

INSERT INTO productSale_test
(buyer, productName, qtyPurchased, pricePaid)
SELECT
    buyer
  , productName
  , qtyPurchased
  , pricePaid
FROM productSale

This way you can select the values from one table, and insert them into another.  Please note I have included the column list between the INSERT INTO and the SELECT statements.  That way the interpreter knows which columns to put the selected data into.

Conclusion

This is your first step into loading data into the database.  No matter how complex the load seems, it will always reduce to a simple INSERT statement.  Practice using this statement and there’s nothing you won’t be able to load.

If you have any problems, questions, or concerns, let me know!  I’m here to help!

Previous: JOIN

Next: UPDATE