Oracle for DBA

Dedicated to my 'True Teacher' Apurva Jadhav (Madam).

Tuesday, 12 October 2010 01:53:03 GMT

Inserting row(s) into table. (DML)

Inserting row(s) into table.

Insert is a (DML) Data manipulation Language. Used to inserting row(s) to the existing table. It implicitly commit changes to database.

There are many ways to insert rows(s) in a table.

1) INSERT row(s) with mentioning column

Syntax:-

INSERT INTO table_name (column_name,column_name,...) VALUES (column_data,column_data,...);

EX:-yesterday we had created BOOK table. Today we will insert row to that table.

CREATE TABLE book
(
sno NUMBER,
title VARCHAR2(10),
published DATE
);

INSERT INTO book ( sno,title,published ) VALUES ( 1,'oracle','01-oct-1988' );

Where 'INSERT INTO' is oracle reserved word for insertion.

'book' is the name of a table.

'(sno,title,published)' are names of a column seprated by comma enclosed in parenthesis.

'VALUES' is a oracle reserved word.

'(1,'oracle','01-oct-1988')' are values for a column seprated by comma enclosed in parenthesis. Will be displayed in table. Note:- literals must be enclosed in single quote, whereas numeric need not to be enclosed in single quote.

";" semicolon is used to execute a command.

If your query is OK you will get message " 1 row created ".

-------------------------------------------------------

2) INSERT row without mentioning column

Syntax:-

INSERT INTO table_name VALUES (column_data,column_data,...);

EX:-

INSERT INTO book VALUES ( 2,'mysql','23-oct-1982' );

Few point must be taken care of while performing this type of query--

  1. In this query we havn't mentioned column_name.
  2. You have to enter value for all columns existing in table. If any value for column is missed, query will fail.
  3. you have to take care of datatype of column and value you are entering. miss match of datatype will fail the query.
  4. This type of query is only good for table having few columns. so that you can remember column order and it's datatype.
-----------------------------------------------------

3) INSERT row(s) to specified column

Syntax:-

INSERT INTO table_name (column_name,column_name,...) VALUES (column_data,column_data,...);

EX:-

INSERT INTO book ( title ) VALUES ( 'php' );

  • As we know we have 3 columns (sno,title,published) in book table
  • I have specified only (title) column in INSERT command.
  • It means only value for (title) column will be added to book table and remaining column will be set to NULL.
  • Remember if there is any column having NOT NULL constraint, you haven't included in list. then query will fail.

You can view your inserted row(s) in table by using:

SELECT * FROM table_name;



In: Uncategorised
Permalink : Inserting row(s) into table. (DML)
Comments: 3
Viewed 248 times.





Make a comment

*
Smilies
*
Enter the code shown. 

Praise to Lord Jesus.