INSERT

The SQL INSERT INTO Statement is used to add new rows of data to a table.

There are two ways to insert data into a table:

1.Inserting the data directly to a table.

2.Inserting data to a table through a select statement.

NP:1)When adding a new row, you should ensure the datatype of the value and the column matches

2) You follow the integrity constraints, if any, defined for the table.

Inserting the data directly to a table

There are two basic syntaxes of INSERT INTO statement:

Syntax1:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] 

VALUES (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You don’t need to specify the column name in the SQL query if you are adding values for all the columns of the table. But the order of the values is in the same order as the columns in the table.

SYNTAX 2:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

EXAMPLE :

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR

EXAMPLE1:

INSERT INTO EMPLOYEE1 (ID, NAME,START_DATE,END_DATE,SALARY,CITY) 

VALUES (7, 'G', to_date('20160101','YYYYMMDD'),to_date('20160706','YYYYMMDD'),

10000, 'HYDERABAD');

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	HYDERABAD

EXAMPLE 2:

INSERT INTO EMPLOYEE1 VALUES (8, 'H', to_date('20160101','YYYYMMDD'),

to_date('20160606','YYYYMMDD'), 10000, 'KOLKATA');

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	HYDERABAD
8	H	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	HYDERABAD

Inserting data to a table through a select statement.

INSERT INTO first_table_name [(column1, column2, ... columnN)]

   SELECT column1, column2, ...columnN

   FROM second_table_name

   [WHERE condition];

To insert one row into the EMPLOYEE1 table from a temporary table, the sql insert query would be like,

INSERT INTO EMPLOYEE1 [(ID, NAME, START_DATE, END_DATE, SALARY, CITY)]

SELECT TEMP_ID, TEMP_NAME, TEMP_START_DATE, TEMP_END_DATE,

TEMP_SALARY, TEMP_CITY

   FROM TEMP_EMPLOYEE1

   [WHERE condition];

If you are inserting data to all the columns, the above insert statement can also be written as,

INSERT EMPLOYEE1 SELECT * FROM TEMP_EMPLOYEE1;