Inserting Data into a Table in SQL: Step-by-Step Guide
Table of Content:
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
Syntax
There are two basic syntaxes of the INSERT INTO statement which are shown below.
The following SQL statement is syntax for INSERT INTO database
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);
Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data.
You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example
INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Rumman','Ansari', 32, 'Ahmedabad', 2000.00 ); INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Musar','Mondal', 25, 'Delhi', 1500.00 ); INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Osman','Sk', 23, 'Kota', 2000.00 ); INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Badsha','Roy', 25, 'Mumbai', 6500.00 ); INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Alamgir','Roy', 27, 'Bhopal', 8500.00 ); INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Rajesh','Roy', 22, 'MP', 4500.00 );
You can create a record in the Human table by using the second syntax as shown below.
INSERT INTO Human VALUES (7, 'Rambo','Azmi', 24, 'Indore', 10000.00 );
After installation it will show like the below
SQL> INSERT INTO Human (ID,FIRST_NAME,LAST_NAME,AGE,ADDRESS,SALARY)
2 VALUES (2, 'Musar','Mondal', 25, 'Delhi', 1500.00 );
1 row created.
All the above statements would produce the following records in the Human table as shown below.
---------- -------------------- -------------------- ---------- ------------------------- ----------
ID FIRST_NAME LAST_NAME AGE ADDRESS SALARY
---------- -------------------- -------------------- ---------- ------------------------- ----------
1 Rumman Ansari 32 Ahmedabad 2000
2 Musar Mondal 25 Delhi 1500
3 Osman Sk 23 Kota 2000
4 Badsha Roy 25 Mumbai 6500
5 Alamgir Roy 27 Bhopal 8500
6 Rajesh Roy 22 MP 4500
7 Rambo Azmi 24 Indore 10000
---------- -------------------- -------------------- ---------- ------------------------- ----------
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "FIRSTNAME", "LASTNAME", and "ADDRESS" columns:
But Remember other columns should not be NOT NULL columns
INSERT INTO Human (FIRST_NAME,LAST_NAME,ADDRESS)
VALUES ('ib','MAM','Delhi');
Populate one table using another table
You can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.
Here is the syntax :
INSERT INTOFirst_Table_Name[(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROMSecond_Table_Name[WHERE condition];
Insert Data into Client_master_21 table
------ | ---------- --------------- | ---------- | --------------- | ----------
CLIENT | NAME | PINCODE | STATE | BAL_DUE
------ | ---------- --------------- | ---------- | --------------- | ----------
C00001 | Amit Saman | Kolkata | 700001 | West | 15000.5
| ta | | | Bengal |
C00002 | Tapos Das | Mumbai | 400012 | Maharashtra | 0
C00003 | Anup Maiti | Mumbai | 400014 | Maharashtra | 5000
C00004 | Bimal Roy | Chennai | 600018 | Tamil Nadu | 0
C00005 | Moni Kar | Kolkata | 700017 | West Bengal | 2000
C00006 | AR Khan | Delhi | 700024 | Delhi | 0
------ | ---------- --------------- | ---------- | --------------- | ----------
Sql Query
INSERT INTO Client_master_21 VALUES('C00001','Amit Samanta','','','Kolkata',700001,'West Bengal',15000.50);
INSERT INTO Client_master_21 VALUES('C00002','Tapos Das','','','Mumbai',400012,'Maharashtra',0);
INSERT INTO Client_master_21 VALUES('C00003','Anup Maiti','','','Mumbai',400014,'Maharashtra',5000);
INSERT INTO Client_master_21 VALUES('C00004','Bimal Roy','','','Chennai',600018,'Tamil Nadu',0);
INSERT INTO Client_master_21 VALUES('C00005','Moni Kar','','','Kolkata',700017,'West Bengal',2000);
INSERT INTO Client_master_21 VALUES('C00006','AR Khan','','','Delhi',700024,'Delhi',0);
Insert Data into Product_master_21 table
------ | --------------- | -------------- | ---------- | ----------- | ---------- | ---------- | ----------
PRODUC | DESCRIPTION | PROFIT_PERCENT | UNIT_MEASU | QTY_ON_HAND | RECODE_LVL | SELL_PRICE | COST_PRICE
------ | --------------- | -------------- | ---------- | ----------- | ---------- | ---------- | ----------
P00001 | 1.44 | 5 | piece | 100 | 20 | 525 | 500
| floppies | | | | | |
P03453 | 6 | 5 | piece | 10 | 20 | 3 | 11280
P06734 | 5 | 5 | piece | 20 | 20 | 5 | 100
P07868 | 5 | 5 | piece | 10 | 20 | 3 | 1000
P07885 | 25 | 5 | piece | 10 | 20 | 3 | 5100
------ | --------------- | -------------- | ---------- | ----------- | ---------- | ---------- | ----------
SQL Code
INSERT INTO Product_master_21 VALUES('P00001','1.44 floppies',5,'piece',100,20,525,500);
INSERT INTO Product_master_21 VALUES('P03453','6',5,'piece',10,20,3,11280);
INSERT INTO Product_master_21 VALUES('P06734','5',5,'piece',20,20,5,100);
INSERT INTO Product_master_21 VALUES('P07868','5',5,'piece',10,20,3,1000);
INSERT INTO Product_master_21 VALUES('P07885','25',5,'piece',10,20,3,5100);
Insert Data into Salesman_master_21 table
INSERT INTO Salesman_master_21
VALUES('S00001','Krim','A/4','Alipore','CityA',700012,'StateE',3000,100,50,'Good');
INSERT INTO Salesman_master_21
VALUES('S00002','Azad','65','Barabazar','CityB',700001,'StateF',3000,200,100,'Good');
INSERT INTO Salesman_master_21 VALUES('S00003','Samir','p-
7','Chadni','CityC',700022,'StateG',3000,200,100,'Good');
INSERT INTO Salesman_master_21
VALUES('S00004','Anindya','A/7','Saltlake','CityD',700091,'StateH',3500,500,150,'Good');