SQL tutorial 33:How To Insert Data into a Table Using SQL INSERT INTO dml statement

Key Takeaways

What's up internet? I'm back once again with another SQL tutorial. In today's SQL tutorial, we will learn how to insert data into a table using insert DML statement. This is the second tutorial in the how to insert data into a table series. Before we start, I want to make one little request. Please subscribe to my channel and like my videos. You can also share my videos on your social networking and help me in reaching to more people. Thanks. Let's start today's tutorial. For the demonstration of insert statement, I have already created a table. Actually this is the same table student which I had used in the last tutorial. Let's see the structure of student table. This table has four columns where column 1 is two ID is a primary key and column 2 first name has not null constraint. Before moving ahead let's take a look at the syntax of insert DML statement. You can use insert statement in two ways. First when you want to insert data only into some specific columns. Here is the syntax. 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 column enclosed in parenthesis. And after that, we have our keyword values followed by the value of the column which you want to insert again enclosed in parenthesis. Second, when you want to insert data into all the columns of your table. And here is the syntax. 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 column name, then Oracle engine will automatically assume that you want to insert value in all the columns of the table which you have specified. Thus it become mandatory for you to supply value for all the columns of your table. Failing to do so will result in error. There are few restriction with both these syntax which you can read on my website. link is in the description box. Hope syntax is clear. Let's once again jump to the SQL developer and do some query. Query one, insert data into all the columns of table student. As I have already told you that I'll be using same student table which I have created in the last tutorial. Table student has four column and we will be inserting values in all these four column. Let's write the query. But before that, let me adjust this SQL developer IDE. This is our student table and as you can see right now we don't have any data inside this student table. Now let's write our first query. This is a very simple query. We are inserting value in all four columns of our table student. But as I have already told you while explaining the syntax that when we are inserting values in all the columns of a table then specifying column names are not mandatory rather it is optional. Thus we can modify this query and delete the column name list followed by the table name student. Just to show you the difference what I will do is that I will copy and paste the query and modify it. So let's copy this query and I will paste it here. And now we will modify it. What we have to do? We just need to delete this column name list. That's it. Both these queries are the same and perform the same task of inserting value in all the four columns of the table. Let's execute both of them one by one. Let's execute the first one. One row inserted. Now let's refresh and see the result. Here is our first row in the table student. Now let's execute the second query. But before that we have to change the value of S2 ID column as it's a primary key. So we cannot enter a duplicate value. By the way I have done a video on primary and foreign key constant. If you want you can watch them. I will put the link in the description box. Okay. Now let's quickly modify this query. What we have to do? We have to change this value from 1 to two. You can specify any number here. Okay. Now execute this query. One row inserted. Let's refresh here. As you can see here is our second row. As you can see both the query perform the same task and enter the value in all the four columns of student table. There are few things which you should always remember such as all the column name and their value must be separated using commas. Order of values must match with the order of column name. In case you are not specifying column name as we did in the second query, then the order of column must be exactly the same as that of the order of column in your table. and date and character values means the column with the data type char or wild chart and date must be enclosed in single quotation mark. You can visit my blog and read the full list of restriction on insert statement. I suggest you to read as this will prevent you from writing wrong insert query. Now let's write our second query. Say we just want to enter data only into STO ID, first and last name column and not into the contact column. We can only omit last name and contact column as these both column allow null values. But column is 2 id is a primary key and first name has not null constant. Thus these two are mandatory column. Let's write the query. Let me make some space. As I told you while explaining the syntax that when you want to insert data into selected or say 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. Let's execute the query. Here we go. One row inserted. Let's refresh and check out the result. Here is our third row. As you can see we have values in our first three column id which is three first name which is super last name which is men but the last column has null value which is contact. Hope you learned something. In the next tutorial we will see the third way of inserting data into a table. Please do comment your feedback. That's it for today. Hope you guys enjoyed watching. Don't forget to subscribe and give me thumbs up if you like this video and share with your friend too. Thanks. We'll see you soon. This is Manish from Rebellion Rider.