SQL INSERT Statement

The this tutorial we will talk about the insert statement which is  statement is used to insert new records in a table. We will use the Test table which has the following structure:

Name  (of the column)       Type(type of data the column has)
 -------------------  ----------------------------------
 ID                  NUMBER(38)
 NAME                 varchar(20)
 EMAIL                varchar(20)

Insert general syntax :

INSERT INTO

VALUES (value1, value2, value3);

In this case we will need to provide values for all the columns of the table. Example:

Sql:>Insert into test values (1,'Eve','[email protected]');

As you can see there is no specification of the fields we want to insert data into, so we need to provide values for all the columns of the table.

INSERT INTO

(column1, column2, column3) VALUES (value1, value2, value3)

In this case we chose witch columns we want to insert data into. Example:

Sql:>Insert into test (id, name) values (1,'Eve');

Notice we only provide values for columns that are provided in the statement. Example:

Sql:>insert into test(email) values('[email protected]');

As well we can insert only one value; like said in the upper example :we need to provide values for columns that are provided in the statement. Wrong statements : Example:

Insert into test values (1,'john');
 Error: insufficient values;

Reason : the table has 3 columns and we only provided values for 2 of them. Correct statement:

Insert into test values (1,'john','[email protected]');

Example:

Insert into test(id, name)
      values (1,'Eve','[email protected]');
Error: to many values;

Reason: we provided more values then columns; Correct statement:

Insert into test(id, name) values (1,'Eve');

More complex insert statement we will learn later on our tutorials.