SQL Basics - Inserting Data into a Database

Up until now, we dealt with retrieving data. Now we're going to discuss inserting and updating the database data. If we just want to add a record to the table, we use the INSERT clause, or INSERT INTO. There are basically two syntaxes that can be used. I will describe both ways.

INSERT INTO Products
VALUES ('BNBG04','DLL01','Monkey bean bag toy',4.49,'Cute monkey toy that your kids will love')

This will add a full new record to our Products table. When using this simple format, you must specify a value for all columns in the table. If you don't have a value to enter, you must enter it as NULL.

There is another format that you can use, and it is more recommended, although a bit more tedious.

INSERT INTO Products(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES ('BNBG05','DLL01','Monkey bean bag toy',4.49,'Cute monkey toy that your kids will love')

The difference here is that we provided the column names that we are inserting to. This is recommended because then the values don't have to be in the order of the columns in the table, so if the table changes a bit, the insert will still work correctly. Also, if we explicitly state the column names, we can leave out some columns if we have no info for those columns. For example, if we don't have a product description we just leave it out of the column list and the values list.

There is another useful way to use INSERT INTO, and that is together with a SELECT clause. It automatically inserts the data retrieved from a SELECT query. Here's an example. We have products in another table names otherProducts and we would like to import it into our Products table. Here's how it's done

INSERT INTO Products

(prod_id,vend_id,prod_name,prod_price,prod_desc)

SELECT(prod_id,vend_id,prod_name,prod_price,prod_desc)

FROM otherProducts

One more thing for this lesson. If we want to copy results of a query into a brand new table, we use SELECT INTO. For example, to create a table with all products below $7.00, we would do this:

SELECT *

INTO newtable

FROM Products

WHERE prod_price<7.00

And get this: