Inserting Data

The INSERT INTO statement is used to insert new rows into a table.
Syntax:

INSERT INTO table_name
(column_name1, column_name2, ....)
VALUES (value1, value2, ....);

Below is a table named, Emp_Add:

ssn	fname	lname	address	city	state
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio
102254896	Sam	Jones	33 Elm St.	Paris	New York
876512563	Sarah	Ackerman	440 U.S. 110	Upton	Michigan

The SQL query to add a new row in this table would be:

INSERT INTO Emp_Add
(ssn, fname, address, city, state)
VALUES (202354897, 'Svendson', '82 Broadway', 'Bayonne', 'New Jersey');

This query inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Note that the value of SSN is written without quotes while all the other values have quotes around them. All the text values need to be enclosed within quotes and numeric value are written without quotes. In this case, no data will be added in the column, "lname".

Output:

ssn	fname	lname	address	city	state
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio
102254896	Sam	Jones	33 Elm St.	Paris	New York
876512563	Sarah	Ackerman	440 U.S. 110	Upton	Michigan
202354897	Svendson		82 Broadway	Bayonne	New Jersey

If you want to insert data in all the columns then there is another way. You don't have to mention the names of all the columns, only the values of the columns have to be entered in the correct order. Let's have a look at an example.

INSERT INTO Emp_Add
VALUES (202354897, 'Svendson', 'Ola', '82 Broadway', 'Bayonne', 'New Jersey');

Output:

ssn	fname	lname	address	city	state
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio
102254896	Sam	Jones	33 Elm St.	Paris	New York
876512563	Sarah	Ackerman	440 U.S. 110	Upton	Michigan
202354897	Svendson	Ola	82 Broadway	Bayonne	New Jersey

To insert system date in Oracle:

INSERT INTO company_events (event_name, event_date)
VALUES ( 'Event 1', SYSDATE );