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.

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:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1

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:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	12

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:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	12
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	3
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2

Posted in: SQL