SQL Insert Statement

SQL Insert Statement is used to add rows to a table.

There are two ways:

  1. With the VALUES keyword
  2. Combined with Sql Select statement

Adding with VALUES keyword

The syntax to insert a row into a table:

INSERT INTO table_name[(column_list)]
VALUES      (value_list) 

That SQL Insert will add a new row to the table and each column in the column list will get a value in the value_list.

Let's add a row to our Authors table:

INSERT INTO authors
VALUES      ('10001',
             'Stann',
             'Dupp',
             '622 9912 621',
             '1221 Pole St',
             'Sydney',
             'NSW',
             '2000') 

The important things to remember when using the Sql Insert are:

  1. If you insert values for all columns, you can omit the column list. The data values must have the same order as when you created them using Sql create table.
  2. Values for character fields must be enclosed by parentheses, single or double depending on the system.
  3. Separate the values by comma.
  4. Separate INSERT statement for each row to add

When you have only partial information but you still want to add them to the table, then you need to specify those columns in the column_list. You can omit columns only when the columns you omitted allow null values. The syntax is the same as above, you just need to list the columns and put the corresponding values in the VALUES list.

Adding with Sql Select Statement

You use Sql Select statement in an Sql INSERT statement in several situation, such as:

  1. To move contents from old table to a new table
  2. To merge two similar tables together.
  3. To create a temporary table based on an existing table
  4. You want to add a calculated field (using certain function)
  5. To combine two or more tables together and insert the values into another table

The syntax is:

INSERT INTO table_name[(column_list)]
SELECT column_list FROM  table_name WHERE conditions

Both column_list must have the same column order.

Say you have two tables as follow:

Table Tbl_L
IDAB
12030
24050
36070
 
Table Tbl_R
IDAB
47075
58085
69095
 

And you want to merge table Tbl_R into table Tbl_L. To do this use the following command:

INSERT INTO Tbl_L(ID , A , B)
SELECT ID , A , B FROM  Tbl_R

This will insert all rows from Tbl_R into Tbl_L.

But, what if you only want to add rows from Tbl_R that have ID less than 6? Then add a WHERE clause.

INSERT INTO Tbl_L(ID , A , B)
SELECT ID , A , B FROM  Tbl_R WHERE ID < 6

That SQL Insert statement will add two rows from Tbl_R into Tbl_L.

Next, what if you noticed that row 2 in Tbl_L was incorrect. The value for Column B should be 55 instead of 50. Which command do you use? If you want to change existing rows, use Sql Update command that we'll discuss next. Let's do it.

Perseverance: Cliffhanger
Buy at AllPosters.com
What the mind can conceive and believe, it can achieve.

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.