SQL Tutorial - 13: Inserting Data Into a Table From Another Table Video Lecture | Microsoft SQL for Beginners - Database Management

hello guys welcome to my channel this is
the 13th tutorial in this course and in
this tutorial we are going to learn to
populate one table using the data that's
present in another table right so the
statement that we are going to use to do
this is the insert statement and we
checked out the insert into query in the
last tutorial when we learnt to put data
in a table that doesn't have any rows or
even if it has rows you can use the
insert into state statement to insert
data in a table one row at a time right
so we've seen how it works and the
syntax of the insert query to populate
one table from the data that's present
in another table is slightly different
from the syntax that we've seen right so
it has a couple of additional keywords
and one thing that you have to make sure
before you do this is that the
destination table or the table in which
you are going to put in your data has to
be compatible with the table from which
you are pulling your data right so once
we see this command in action in the
workbench this will become clear to us
right and the syntax as you can see you
have to use your keywords insert and
into and then you have to type in your
table name and then you know you specify
the columns if in case you want to put
in your data in a you know in a few
columns and not in all the columns and
then you use the keyword select and you
type in the names of the columns from
which you want to pull in your data and
then you use the keyword from and you
type in the name of your source table
and in case you want to have some weight
loss you can mention that too but this
is optional that's why it's in square
brackets right so let's see how this
works in the MySQL environment so I've
got my MySQL workbench here and there
are a couple of ways in which you can
check whether you have any tables in
your database or not the first one is
obviously you can look into the object
browser you can click on the drop down
for your database and then click on the
drop down for tables and as you can see
at the moment I've got two tables in my
database the first table is the EMP
underscore info table that I created
just for the sake of this tutorial and
the second one is the
ploy table that we created some
tutorials ago when we learned the create
table statement right so the structure
of the EMP underscore info table and the
employee table is absolutely same and
there's one command and I want to
discuss that's got nothing to do with
this tutorial and I should have told you
guys this statement you know some
tutorials ago actually but I it just I
just missed it so the command is show
tables and I mentioned it but I didn't
the you know execute it and show you
guys what it does so when you execute
this command you see all the tables that
you have in your database so in the
results set I see that at the moment I
have EMP underscore info table and the
employee table right so that's all it
does and in case you do not want to use
the object browser or if you're using
the MySQL command line client then you
know this is a very handy command right
so that's enough about it now I'll show
you guys the structure of both the
tables so that you can see that the you
know two tables are mutually compatible
so to see the structure I'll obviously
use the D ESC command so we will first
see the structure of employee right
because this is going to be our source
table and it has four fields ID name DOB
and email and ID field is of integer
type name is of archetype do B's of
date/time type and emails of archetype
right and now I'll show you the
structure of the EMP underscore info
table right so de SC and then employ EMP
underscore info and when I press control
enter I see that this table also has the
same a you know set of columns but it
doesn't have the primary key constraint
on the ID Friel and you know the two
tables can have different set of
constraints but what's important is that
you know the columns from which you are
pulling your data from the source table
and the columns in which you are putting
your data in the destination table those
columns need to be mutually compatible
right so if you put pulling in data from
the ID field which is of integer type
then you must put in your data in some
field in your destination table that is
also of ID type right so I hope I'm
making sense and
the another thing that we need to see
before we actually execute or insert
into statement is that whether the EMP
underscore info table has any data in it
or not at the moment right so to do to
do that I'll use the Select star query
select star from EMP underscore info and
press control enter and there you go you
see that the table currently doesn't
have any you know information in it and
I'll also show you guys the output of
the Select star query when executed on
the employee table right because we
should know what information we are
actually going to pull in from this
table and it has two rows the first one
is a row with ID 1901 and the second one
is a row with ID 2,000 right so we're
going to pull in both these rows and
we're going to put this information in
the EMP underscore info table using the
insert into statement and since we don't
need any of these anymore we'll be
requiring one select star query to make
sure that the insert statement has been
executed successfully or not but that
we're going to need later on so for the
time being I'll type in insert into the
name of my table which is EMP underscore
info and then I type in the name of the
columns in which I want to put in my
information right so it's ID and then
name and then DOB and then email next
I'll type in the Select keyword and the
names of the columns of the source table
and the columns are ID name DOB and
email and then on the next line I'll
type in the keyword from and the name of
the source table which is employee and
put in a semicolon to terminate the
statement and when I press the ctrl
enter key combination I see that in the
output section I get the message insert
into statement has been executed
successfully and the message is that two
rows have been affected right and we can
see what all things have been copied by
executing the Select star query on the
EMP underscore info table so the query I
select start from EMP
underscore info putting a semicolon to
terminate the statement why I press the
control into a key combination I see
that in the result set
I get that the EMP underscore info table
now has two rows so that's it for now
thank you so much for watching this
tutorial I'll see you guys in the next
one please subscribe to my channel in
case you haven't already and take care