Bookmark and Share

SQL - Insert

To use the INSERT command, we must first have an understanding of where we would like to insert data and what types of data we want to insert. Do we plan on inserting numbers? Strings? Files? Let's return to the orders table we created in an earlier lesson.

Advertise on Tizag.com

SQL tables store data in rows, one row after another. The INSERT command is the command used to insert new data (a new row) into a table by specifying a list of values to be inserted into each table column. The arrangement of values is important, and how they are arranged in the code corresponds to how the data values will be arranged in the the SQL table.

  • id - (identity, integer)
  • customer - (customer name, character string)
  • day_of_order - (date value)
  • product - (name of product, character string)
  • quantity - (quantity, integer)

Looking at the column names alone will give you an idea of what type of data each column is expected to hold. The quantity column, for example, is expecting a number or integer of some sort and the day_of_order column is expecting a date value to be inserted.

SQL Insert Query:

USE mydatabase;

INSERT INTO orders (customer,day_of_order,product, quantity)
VALUES('Tizag','8/1/08','Stapler',1);

SQL Insert Results:

(1 row(s) affected)

You may notice that the id column has been left out of the query statement. The reason behind this is that when we created the orders table, we gave the id column a unique attribute called identity. SQL handles identity columns automatically for us and therefore, we do not need to manually insert data into this column.

The first value Tizag corresponds with the customer table column. This ensures SQL will insert this value into the corresponding table column.

Now when we run the SELECT (*) query, SQL should return two rows with our statement instead of only a single row.

Verification Query:

USE mydatabase;

SELECT *
FROM orders;

SQL Results:

idcustomerday_of_orderproductquantity
1Tizag2008-08-01 00:00:00.000Pen4
2Tizag2008-08-01 00:00:00.000Stapler1

SQL - Inserting Values

As a shortcut, you may omit the table columns entirely and only supply the values in the INSERT statement:

SQL Insert Shortcut:

USE mydatabase;

INSERT INTO orders 
VALUES('A+Maintenance','8/16/08','Hanging Files',12);

Again, we can skip the id column because SQL is able to identify that this column is an identity column and handle it accordingly.

SQL Results:

idcustomerday_of_orderproductquantity
1Tizag2008-08-01 00:00:00.000Pen4
2Tizag2008-08-01 00:00:00.000Stapler1
3A+Maintenance2008-08-16 00:00:00.000Hanging Files12

Before moving on, let's add some more rows and execute some more INSERT queries. If you are using SQL Express, you should be able to copy the entire code section below and execute all the queries at once and then track the results with the verification query (SELECT * FROM orders).

SQL Inserts:

USE myDatabase;

INSERT INTO orders
VALUES('Gerald Garner','8/15/08','19" LCD Screen',3)
INSERT INTO orders 
VALUES('Tizag','7/25/08','19" LCD Screen',3);
INSERT INTO orders 
VALUES('Tizag','7/25/08','HP Printer',2);

Final Results:

idcustomerday_of_orderproductquantity
1Tizag2008-08-01 00:00:00.000Pen4
2Tizag2008-08-01 00:00:00.000Stapler1
3A+Maintenance2008-08-16 00:00:00.000Hanging Files 12
4Gerald Garner2008-08-15 00:00:00.00019" LCD Screen3
5Tizag2008-07-25 00:00:00.00019" LCD Screen3
6Tizag2008-07-25 00:00:00.000HP Printer2
Bookmark and Share




Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials