Hi guys this is Mina and welcome to my channel. in In today's video we are going to learn about the sql
insert into select statement. so this statement is used to copy data from one table and insert
into another table and for it to work both table 1 and 2 need to exist either in the same database
or in different databases and also if the data types between both tables are different, this
syntax is going to throw an error. what i mean is for instance, if i have to move a field called name
with data type varchar from table one to table two then in table two, the field also needs to have
varchar data type. if the data types between both tables are different this syntax is gonna throw an
error. so the sentence for the insert into select statement is you write out the word insert into
and then you specify the destination table or table 2 and the columns that you want to populate
within table 2 and then you select columns from table 1. so insert into table 2 columns in table 2
and then select columns from table one it's that simple guys so let's move on to our SQL server
management studio and try this out so here in my SQL server management studio, i have
a database called demo and i have some tables here what i want to do is to move records from
people into another table called persons for the insert into select statement to work
means that i need to create persons table so let's go to new query and
paste the syntax for insert into select statement here. first of all i need to create the person
table and the easiest way for me to create this table is to script out the existing table
so i'm just going to go to this table and script table as read to copy to clipboard
and paste the syntax here all i need to do now is to change the name of the table to persons
and execute this command i need to make sure i'm working in the demo database and
click on execute now if i refresh the demo database and go to tables i have my persons
table here. i'm going to increase the size let me create a new query window here so i'm going to
insert table into person table by writing out insert into select statement. so insert into person
table that's table two, my destination table, the columns in the person table
okay so i'm going to insert into, all of these columns however the id field
is a primary key and i need to check if it's an identity field from the syntax here
it's not an identity field so i have to populate it if it was an identity field then
there is no need to populate it will auto populate by itself so i'm going to insert into the id field
the name field
gender
date of birth
and city
and i'm going to select record from table 1 so select columns same columns
from
persons and that's it. which means that every record i have in the person table will be moved
into the persons table. do we want to see the records yes i do so let's just select * from
people table just see the records we have in there execute so that means all of these records
will be moved into the persons table i'm going to go ahead and insert into the persons table
10 rows have been affected so if i select * from
persons table we shall see the same records in there
there we have it guys it's so easy and simple. so here in our first demonstration
we copied data from from one table to another that exists in the same database
but with this syntax we can even work across databases
and we can do that by using the three-part naming convention which is we specify the database the
schema and the table so let's try that out our next example will be moving data from
one database to
another okay so let's look for
a table in essential think i have a simple table here, country
yes country exist in essential database so i'm going to script out
country table
the syntax for creating the country table i'm gonna create this table in demo database
one thing we need to note from this syntax is that, the country id is an identity field which
means that in our insert into select statement we do not need to specify this country id column when
we are moving records into our table two because it will auto populate by itself so now execute
command. command has been completed successfully if i refresh the demo database and open it up
i have a country table here, so now insert into
database name
and then the table name so this is a three part naming convention. database, schema, table
if we do this then we can work across databases because we are specifying the name of the database
and what what columns in um country table do we want to populate, i'd like to populate
just the country name okay then i'm going to select the country name from the essential
database so let's go there. i'm going to collapse the demo database, open the essential database
columns. select country from essential dot dbo country it's that simple with that we don't
even need to specify database here because the system will automatically detect that we are
working across databases okay i'm gonna execute this command 6 rows have been affected
do you want to know the number of rows in the essential database? select *
from without specifying the essential database the query will not work so i'm just gonna put the
essential database here special dot country is okay so we have sales records in our
essential country database let's select from our newly created
table in the demo database so because the query window has been set to demo we don't need to
um include the database name here but if you want you can still put demo dot here so i'm just gonna
execute this command see we have the same results so this is essentially how you copy data from
one table to another either within the same database
or across databases and using the insert into select statement
thank you guys so much for watching this video and see you in my next one bye-bye