Hi guys this is Mina and welcome back to my channel in today's video i'm going to teach
you the select into statement in sql server. the select into statement allows us to copy data
from one table to another without having to explicitly create the new table that we
are copying into. so once we execute a command the new table gets created and it will contain
the same columns that are specified in our select statement as well as the data type it's going to
have the same column specified and the data types from the old table if you however want to change
the column names you can use the "as clause" and then provide a new column name so here in the
syntax for select into statements we have select columns as many columns as you want to. select
into the new table no need to create this new table you just specify it in this
syntaxand then it gets created once you execute the syntax. okay so into new table from old table
if you want to change the column name just use the as clause and then specify the name column name
it's that simple okay so let's move on to our sequel server management studio and try this out
so here in my sequel server management studio i'm gonna be moving data from the employees table
in the essential database into the select into database and to do that you want to click on new
query here and i'm gonna paste the syntax for select into here so we can refer to it. first of
all let's select from this employees table and see what data we have in there so i'm going to select
i'd rather drag the columns here so that i don't make any mistake in typing them out
and you can select as many columns as you want you don't need to select everything
it depends on the requirement. okay from employee i'm gonna make sure you are in the right database
by clicking on this drop-down select the database that contains the table you are
selecting from and then click on execute or f5 so we have 604 records in the employees database
if you want to copy all the data from the employees into our select into database then
we are going to type out the word into and then provide the new table name and this new
table will get created once we execute this command so i'm just going to say new table
from old table know that if i execute this command because i didn't specify the database it's gonna
the new table with all these columns is gonna get created in my essential
database not this select into database i can execute this comment to show you because we are
working in essential if i execute this command 604 rules have been affected but where did the table
go? well it will get created in the essential database so i'm gonna refresh this database
and click on it go to tables and then we have a new table here
to show you if i go into the select into database oops i want to create the new table in the select
into database but because i didn't specify the database here i ended up creating this table in a
wrong database so how do i do that you simply type out the database name and separate it with a dot
here this table has the schema dbo which is the default schema that sequel server provides so it's
dbo and from the from oh okay so you can see here there's dbo here so here too i have to specify the
essential database here as well so basically what we are saying is select these columns
into this database with this schema and create this table select into the new table these are
all part of the new table name this is what is usually referred to as three part naming of a
table in sql server and also from this database schema and table if i execute this command
604 rows affected and if we refresh our select into database
you'll find out that the new table has now been created here if you want to select *
from this new table we can do that and see if it actually contains the columns that we specified
make sure you're working in the right database
and as you see 604 rows and it has the same column names here remember you can reduce
the number of columns by using the web clause so let's say where um employee id
just gonna select it from here where employee id hmm let's say in
just gonna select some of these employee ids
and because this is a string we need to put it in quotation single quotes here
just gonna copy three of this into a new table to show you that it still works with the where clause
okay so i'm just gonna new table two and let's give this last name a new name. as surname
okay so surname no 'e'. okay so if i execute this command we are gonna have the new table that
is called new table two which is different from what we originally originally created new table
okay and because we have specified the database name it doesn't matter which database we are in
the syntax will still work and the query will get created
see we selected three rows because we specified where
employee id are in one two three so three rows affected now let's select from new table two
before that let me just refresh or select into database and show you that a new table with the
name new table two has been created here okay so we are going to select * from new table
two and i'll select into database there we have it guys so this is an easy way for you to
create a table without having to explicitly write centers to create a table you can just select
um records from one table into another and it works like magic thank you so much for being with
me on this video and thank you for subscribing to this channel see you in my next video bye-bye