SQL tutorial 34: How to copy /Insert data into a table from another table using INSERT INTO SELECT

Key Takeaways

what's up internet I'm Manish from rebellionrider.com and I'm back once again with another SQL tutorial and in today's tutorial we will learn how to insert or copy data from one table to another using SQL insert into statement or say insert into select a statement in the previous tutorial we have seen how to insert data into a table using SQL Developer as well as using insert DML statement today we will see the Third Way of inserting data into a table without Much Ado let's jump over SQL Developer and start writing cury as you know we have a student table which we have created and used in the previous tutorial and I have populated this table with some data let me show you this student table will be our source table whose data we are going to copy into a Target table now we need this target table this target table should have same structure as that of student table to save the time I have already created this target table by the name of student copy and for the sake of Simplicity there are no constant on any column of this table let me show you the structure of student copy Target table and as you can see this table is empty this whole process of copying rows from one table to another involves only one simple insert into C let's see how to write this there is no need to be intimidated by this little bit of subury concept involved here I'll be doing a separate tutorial on subquery don't worry for now here in this cury we are inserting the entire data of table student which is our source table into table student copy which is our Target table for the better understanding of this concept let's make some space between these two lines now you people can see things clearly as you can see this query is a combination of two queries in the first half of this query we have our insert DML statement followed by name of Target table student copy and in the second half we have a simple select statement where we are selecting entire data from Source table student before execution let me adjust the SQL Developer IDE so that I can show you the execution side by side okay everything is set now as you can see targetable student copy is entirely empty now let's execute our cury four rows inserted okay let's refresh here is our result isn't it simple entire data of the source table student is copied into the target table student copy let's do our second query but first let me close this output panel so that I can make some space and you people can see what I'm going to do next okay I think everything is set now now in our second query we will see how to insert selected data into a table from another table but before writing this query I will remove entire data from the target table student copy so that you can better understand the concept say this time you just want to insert data from column first name and last name of source table student into Target table student copy okay let me make some space now I think C is clearly visible to you guys okay let's move ahead this cury is slightly different from the previous Cy whenever we insert data into some selected Columns of a table we have to mention those column in the insert cery right after name of Target table as we did here as you can see right after the name of Target table student copy I have specified the name of column in which I want to insert the data and in our case it's first name and last name mind here do not to forget and close the list of column name in parenthesis otherwise SQL compiler will give you an error after that we have our selected statement this statement will return return first name and last name which we want to insert let's execute the query now refresh here and here we go as you can see in the result all the value in is2 ID and contact column is null there are values only in first and last name column what happened with this query is that the output of selected statement becomes the input of the inserted statement let's take the example of this query here our selected statement is returning value from first and last name column which is its output the output becomes the input offers insert cury furthermore there is one-on-one mapping between the column in this list this means means that value from the first column in the select list gets copied into the corresponding First Column you mention in the insert list hope this query is clear let's jump on the third cury once again let me close this script output now let's write the third cury similarly you can use we Clause to copy the data from one table to another but once again let's delete all the data from the table okay everything is set say now you want to copy only that Row from the student table where true ID is five let's execute it says zero rules inserted means let me check okay we do not have any fifth row in our source table student so so what we will do we will change this five from four let's copy the data of a student where 2 ID is 4 let me check who is there student with 2 ID 4 is Batman with 45678 contact number okay oh sorry yes our Target table student copy doesn't have anything so now let's execute this query one row inserted so now let's refresh here is our result hope you enjoyed watching that's it for today there is no reason of making videos if you don't like the content which I am producing so please hit the thumbs up button and keep me motivated comment your feedback and please don't forget to subscribe take care this is Manish