Hey guys! This article is a part of my Insert Series and is in continuation of my earlier article on How to insert data into a table using SQL developer.
Insert is a DML statement which helps in inserting one or more new rows of data in a database table.
You can use INSERT statement in 2 ways.
When you want to insert data only into some specific column table.
For that the syntax will be
INSERT INTO table_name (col1, col2…) VALUES (val1, val2…);
Insert keyword is always followed by INTO keyword thus our INSERT statement starts with INSERT-INTO, followed by the table name in which you want to Insert the row and then we have our list of columns enclosed in parenthesis. Thereafter we have our Keyword VALUES followed by values of columns which you want to insert, enclosed in parenthesis.
When you want to insert data into all the columns of your table.
For this the Syntax will be
INSERT INTO table_name VALUES (val1, val2…);
Syntax is pretty similar to the previous one except that here we do not have a list of column name. When you do not specify the list of the column name then Oracle engine will automatically assume that you want to insert values in all the columns of the table which you have specified. Thus it becomes mandatory for you to supply value for all the columns of your table. Failing to do so will result in error.
There are a few things which you should keep in mind before writing INSERT-INTO Query.
- Insert-Into statement inserts only one row at a time.
- All the column names and their corresponding values should be separated by using commas (,).
- Character and date values must be enclosed in single quotation marks.
- When inserting records into a table using the SQL INSERT statement, you must provide a value for every NOT NULL column.
- You can omit a column from the SQL INSERT statement if that column allows NULL values.
- If you are specifying column names in the query then the order in which the values are being specified must be the same. On the contrary, if you are not specifying the column names even then your order of values should match with the order of the columns in your table.
Now let’s see some examples.
For the purpose of demonstration I have created a very simple table by the name of STUDENT. This table has 4 columns stu_id, first_name, last_name and contact where stu_id column has primary key constraint and column first_name has not null constraint.
Reading suggestion for you: “How to create table”.
Query1: INSERT DATA into all the columns of table ‘student’ (Syntax 2).
INSERT INTO student VALUES (1,’Iron’,’Man’, 12345);
This is a very simple query; we are inserting values in all four columns of our table ‘student’. If you want you can specify the list of column names although it’s not mandatory when you are inserting data in all the columns of your table.
Query 2: Insert Data into selected column of a table (Syntax 1)
Say we just want to enter data only into Student id, first and last name column and not into the contact column.
INSERT INTO student (stu_id,first_name,last_name) VALUES (2,’Super’,’Man’);
As I told you while explaining the syntax that when you want to insert data into some specific columns, and not in all the columns, then you have to specify the column name in which you want to insert the data.
That’s all guys. Hope you find this useful. Please share it with your friends on your social network and help me reach out to more people. Thanks & Have A Good Day!
