Tutorial on SQL INSERT INTO Command

 
The INSERT INTO statement is used to insert new records in a table.
In SQL, there are fundamentally two ways to INSERT data into a table: One is to insert it one row at a time, the approach is to insert multiple rows at a time.In this given section, we are going to look at the first case:
 
The syntax for inserting data into a table one row at a time is as follows:
 
INSERT INTO “table_name” (“column1”, “column2”, …)
VALUES (“value1”, “value2”, …);
 
Assuming that we have a table that has the following structure,

Table Store_Information

Column Name Data Type
Store_Name char(50)
Manager_ID integer
Sales float
Txn_Date datetime

 
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales, and the Manager_ID for this store is 10. We will use the following SQL script:
 
INSERT INTO Store_Information (Store_Name, Manager_ID, Sales, Txn_Date)
VALUES (‘Los Angeles’, 10, 900, ‘Jan-10-1999’);
 
Now the table will hold the following data:

Table Store_Information

Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999

 
Please note that we can specify the column names in any order — the order does not have to be the same as that of the table. For example, the SQL statement before is equivalent to the SQL statement above:
 
INSERT INTO Store_Information (Sales, Store_Name, Manager_ID, Txn_Date)
VALUES (900, ‘Los Angeles’, 10, ‘Jan-10-1999’);

Not Specifying Column Name In INSERT INTO Statement

If we leave off the column names in the INSERT INTO statement, we will need to make sure that the data is inserted in the same column order as that in the table. For example,
 
INSERT INTO Store_Information
VALUES (‘Los Angeles’, 10, 900, ‘Jan-10-1999’);
 
will give us the desired result, while
 
INSERT INTO Store_Information
VALUES (900, ‘Los Angeles’, 10, ‘Jan-10-1999’);
 
will result in Store_Name being set to 900, Manager_ID being set to ‘Los Angeles’, and Sales being set to 10. Clearly this is not what we intended to accomplish.
 

Inserting Into Some Columns

In the above examples, we insert a value for every column in the table. Sometimes, we may decide to insert value into some of the columns and leave the rest of the columns blank.
 
For those cases, we simply specify the columns that we want to insert values into in our SQL statement, such as in the following example:
 
INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
VALUES (‘New York’, 500, ‘Jan-10-1999’);
 
Now the table becomes:

Table Store_Information

Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
New York 500 Jan-10-1999

 
In this case, the value for the Manager_ID column in the second row is NULL. NULL means that data does not exist, and we discuss the concept of NULL later in this tutorial.

Exercises

1. Let’s assume we start with the Store_Information table shown above. What does the table look like after the following SQL statement is executed?
 
INSERT INTO Store_Information VALUES (‘San Jose’,25,700,’Jan-10-1999′);
 
2. Continuing with Question 1. What does the table look like after the following SQL statement is executed?
 
INSERT INTO Store_Information (Manager_ID,Txn_Date,Sales) VALUES (10,600,’Jan-11-1999′);
 
3. Using the same table as above. What does the table look like after the following SQL statement is executed?
 
INSERT INTO Store_Information (‘Portland’,30,650,’Jan-11-1999′);

ANSWERS

1. The table becomes,

Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
New York 500 Jan-10-1999
San Jose 25 700 Jan-10-1999

 
2. The table becomes,

Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
New York 500 Jan-10-1999
San Jose 25 700 Jan-10-1999
10 600 Jan-11-1999

 
3. The table becomes,

Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
New York 500 Jan-10-1999
San Jose 25 700 Jan-10-1999
10 600 Jan-11-1999

 
There is no change to the table as the SQL statement is not valid (it is missing ‘VALUES’).

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.