(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | INSERT INTO Statement
The INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using INSERT INTO statement for inserting rows:
- Only values: First method is to specify only the value of data to be inserted without the column names.
INSERT INTO table_name VALUES (value1, value2, value3,…);
table_name: name of the table.
value1, value2,.. : value of first column, second column,… for the new record - Column names and values both: In the second method we will specify both the columns which we want to fill and their corresponding values as shown below:
INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);
table_name: name of the table.
column1: name of first column, second column …
value1, value2, value3 : value of first column, second column,… for the new record
Queries:
INSERT INTO Student VALUES (‘5′,’HARSH’,’WEST BENGAL’,’XXXXXXXXXX’,’19’);
Output:
The table Student will now look like:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | XXXXXXXXXX | 18 |
2 | RAMESH | GURGAON | XXXXXXXXXX | 18 |
3 | SUJIT | ROHTAK | XXXXXXXXXX | 20 |
4 | SURESH | Delhi | XXXXXXXXXX | 18 |
3 | SUJIT | ROHTAK | XXXXXXXXXX | 20 |
2 | RAMESH | GURGAON | XXXXXXXXXX | 18 |
5 | HARSH | WEST BENGAL | XXXXXXXXXX | 19 |
Method 2 (Inserting values in only specified columns):
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES (‘5′,’PRATIK’,’19’);
Output:
The table Student will now look like:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | XXXXXXXXXX | 18 |
2 | RAMESH | GURGAON | XXXXXXXXXX | 18 |
3 | SUJIT | ROHTAK | XXXXXXXXXX | 20 |
4 | SURESH | Delhi | XXXXXXXXXX | 18 |
3 | SUJIT | ROHTAK | XXXXXXXXXX | 20 |
2 | RAMESH | GURGAON | XXXXXXXXXX | 18 |
5 | PRATIK | null | null | 19 |
Notice that the columns for which the values are not provided are filled by null. Which is the default values for those columns.
Using SELECT in INSERT INTO Statement
- Inserting all columns of a table: We can copy all the data of a table and insert into in a different table.
INSERT INTO first_table SELECT * FROM second_table;
first_table: name of first table.
second_table: name of second table.We have used the SELECT statement to copy the data from one table and INSERT INTO statement to insert in a different table.
- Inserting specific columns of a table: We can copy only those columns of a table which we want to insert into in a different table.
Syntax:INSERT INTO first_table(names_of_columns1) SELECT names_of_columns2 FROM second_table;
first_table: name of first table.
second_table: name of second table.
names of columns1: name of columns separated by comma(,) for table 1.
names of columns2: name of columns separated by comma(,) for table 2.We have used the SELECT statement to copy the data of the selected columns only from the second table and INSERT INTO statement to insert in first table.
- Copying specific rows from a table: We can copy specific rows from a table to insert into another table by using WHERE clause with the SELECT statement. We have to provide appropriate condition in the WHERE clause to select specific rows.
INSERT INTO table1 SELECT * FROM table2 WHERE condition;
first_table: name of first table.
second_table: name of second table.
condition: condition to select specific rows.
Table2: LateralStudent
ROLL_NO | NAME | ADDRESS | PHONE | Age |
7 | SOUVIK | DUMDUM | XXXXXXXXXX | 18 |
8 | NIRAJ | NOIDA | XXXXXXXXXX | 19 |
9 | SOMESH | ROHTAK | XXXXXXXXXX | 20 |
Queries:
-
- Method 1(Inserting all rows and columns):
INSERT INTO Student SELECT * FROM LateralStudent;
Output:
This query will insert all the data of the table LateralStudent in the table Student. The table Student will now look like,ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK DUMDUM XXXXXXXXXX 18 8 NIRAJ NOIDA XXXXXXXXXX 19 9 SOMESH ROHTAK XXXXXXXXXX 20
- Method 1(Inserting all rows and columns):
- Method 2(Inserting specific columns):
INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Output:
This query will insert the data in the columns ROLL_NO, NAME and Age of the table LateralStudent in the table Student and the remaining columns in the Student table will be filled by null which is the default value of the remaining columns. The table Student will now look like,ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK null null 18 8 NIRAJ null null 19 9 SOMESH null null 20 - Select specific rows to insert:
INSERT INTO Student SELECT * FROM LateralStudent WHERE Age = 18;
Output:
This query will select only the first row from table LateralStudent to insert into the table Student. The table Student will now look like,ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK DUMDUM XXXXXXXXXX 18 - To insert multiple rows in a table using Single SQL Statement
INSERT INTO table_name(Column1,Column2,Column3,.......) VALUES (Value1, Value2,Value3,.....), (Value1, Value2,Value3,.....), (Value1, Value2,Value3,.....), ............................. ; table_name: name of the table Column1: name of first column, second column … Value1, Value2, Value3 : value of first column, second column,… for each new row inserted You need To provide Multiple lists of values where each list is separated by ",". Every list of value corresponds to values to be inserted in each new row of the table. Values in the next list tells values to be inserted in the next Row of the table.
Example:
The following SQL statement insert multiple rows in Student Table.
Input : INSERT INTO STUDENT(ID, NAME,AGE,GRADE,CITY) VALUES(1,"AMIT KUMAR",15,10,"DELHI"), (2,"GAURI RAO",18,12,"BANGALORE"), (3,"MANAV BHATT",17,11,"NEW DELHI"), (4,"RIYA KAPOOR",10,5,"UDAIPUR");
Output : STUDENT TABLE
This query will insert all values in each successive row in the STUDENT TABLE . Thus STUDENT Table will look like this:ID NAME AGE GRADE CITY 1 AMIT KUMAR 15 10 DELHI 2 GAURI RAO 16 12 BANGALORE 3 MANAV BHATT 17 11 NEW DELHI 4 RIYA KAPOOR 10 5 UDAIPUR
MySQL Tutorials for Business Analyst: MySQL INSERT INTO Query: How to add Row in Table
Learn to Code SQL Example – SQL | INSERT INTO Statement
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.