hi guys this is Mina, today we are going to learn how to
insert data into tables in sql server management studio we would
first look at how to do that in the graphical user interface and then
i will show you to write tsqlcodes
in order to insert data into tables so stay tuned
this is our sql server management studio interface
and in order to insert data first of all go to the database in which
you want to insert the data into and select the
appropriate database i'm going to use the student record
database we created from our previous video so i'll just
click on the plus sign beside it and then go to tables all right
so we are going to populate the student table in this video
right click on the table we want to populate
and select edit top 200 rules once this edit page appears we can
insert values into this table remember guys that from
our previous video we made this student an identity key so in order
to see that i'll right click on the table
and go to design okay so under design i'll click on
properties you can also click f4 and then we can
see that our table has an identity column
which is student id so because we have identity column we do not need to
enter the ids for this particular column it's an auto generated key
so the system will do that for us so i'll go back to our design view and as
you can see that column has been grayed out so we
just need to enter values into the first name column last name age
email address and gender so let's go ahead and do that
i'm just i'm generating this data just for the
tutorial no special reason okay last name
will be yell i'm gonna give y'all 20 yes email
address yao at whitemail.com
um he's a male okay next i'm going to the second
row and look at the student id you'd see that it would populate by
itself see we have one as the first student ids
okay so let's enter the values here
sarah is the first name last name ken age 23 email address
king edv.edu i'm just randomly populating these
columns with no special reason just for the video okay so once i click
here i expect the student id to be two there we have it
so you see how it works if you are populating a table
um with an auto incrementing id i'm just gonna enter one last record
here so this is how you populate data
in the graphical user interface let's see how populating this table would work
if we turn off the identity key property like i showed you
previously you go to your table and then you right
click on design i already have that window opened here
so i'll just go there and under the identity column i'm
just going to delete this one so right now our student id is no longer
an identity key i would save it
and go on to the edit view and now we can see that we have the
option to enter value in our student id just
because it's no longer an auto incrementing identity key so we can
type the nest id which is four enter the first name
nil amy nil is 25 yes
amy art gmail.com and he's a male so i'm just gonna
enter one more record here okay guys this is how you can enter
values into a table in a sql server management
studio we can simply close this view and the
record should be present under the student
table so let's go ahead and do that and close this as well so now we are
going to refresh the student
table
and select top thousand rows
this is our record that we just entered guys
so we have inserted records into our table using the graphical user
interface now i'm going to show you how to write
the inset into statement within sql server management studio
and to do that i'm going to click on new query
so just click on new query okay so this is the same text
for inserting values into a table in sql server management studio
so this is the insert into statement here
and then you type in your table name and then
into rounded brackets you type in all the columns that you want to
populate um once you enter the columns you have
the option to include or exclude columns
from your insert into statements it's fine
and then you close the rounder bracket type in the word values and then
what values do you want to insert into your table
in the order of how you have listed out the columns
separate them by comma and make sure make sure you put them
into rounded brackets this semicolon here is to
end the insert statement so i'm going to comment out this syntax
by typing in the backslash and star
once i do that for the first part so it means that everything that i'm going to
type is commented so i need to end my
comment with star and backslash again so now let's
type in our code to and set values into a table
so i'm going to just type that out it's in set
into a table name is i'm just gonna drag the name here
you can type it if you want so insert into the table name
and what columns do we want to insert values into put them into rounder
brackets the first column is student id
as we can see the intellisense is helping us with our
column name so i'll just select that and next we have first name last name
once you stop typing the intellisense will help you if that column name
exists okay if you don't want to type it you can just
come to that column and drag it onto your query editor window
when i drag it you can see that i have square bracket around the column name
that is also correct that's what you get if you drag your columns i'm going to
drag email also into the editor window query editor
window and lastly i'm going to populate
gender and then i'm going to type out values what values do i
want to insert into my table because we have turned off the
identity key for this particular table we can easily go ahead and type
the student id because it's a primary key and it cannot
be null the last id we entered was five and remember this
primary key has to be unique you can't duplicate
values in the primary key so you have to continue from
six i have a needle the last name is amy
and she is 24 years
email address amy at t u v
dot c a and anita is a female now i want you to notice something these
column values that i've entered are all underlined it means that
it's an error in sql server management studio
any time you enter a string value or n version vasha sha
data type you need to put it in a single code so we're gonna put all our n-value
values into single quotes and that is how you do it okay
so all of this in single quote
we can go ahead and execute this command but i want to enter one more record
into my table so i'm going to separate this record from the next by
typing a comma and then i'm gonna type out
here into rounded bracket my student id is going to be 7
first name v
remember single quotes for your and virtual values okay last name
lee 27 if it's an integer you do not need to put them in
code so 27 i'm not going to put it into code
but the email address needs to be encoded it's lee
at ch dot a u and
into single quotes lee smile got close okay
we need to close this one as well okay so no error now we can go ahead and
execute this command so we have entered two rows into our
table remember we entered five rows in our
graphical user interface and now we've added two rows so i'm
going to go ahead and select top thousand rows and we should see
seven rows or seven record this time there we have it guys so this is the
record that we have inserted into our table
let's see how we are going to populate this table when we turn on the
identity property so i'm going to right click on the
student and go to design
in the design view i will go to properties
and then table designer identity column select student id
as our identity column and save it you can also click plus s
now that our student id is an identity column
let's go and enter some values into our table and see how it works
remember if your column is an identity column
you don't need to type in those column values system
automatically generated so i'm just going to copy this
code and insert it again here okay now so right now i can take
off the student id
and all the values for student id and execute this command let's see
execute see because our student id is an
identity key we can easily remove the student id
and it should work all right let's see if this command with student
id and student id values will work now that
our student id is an identity key it should give us an
error see you cannot insert explicit values for identity color so
you don't need to insert values for identity column
you can remove it and this is the code that you have to use without the student
id because system automatically generated
let's try out the method where we don't include the column names
that means we have to enter values for all the columns
in the other as the columns appear in the table
so i'm going to delete the column names so insert
into the table name values then i have the student first name
the last name the age email address and gender this should also work for
us because it's in the other as the column up here in the table
and also remember our student id is now an identity okay so we don't need to
enter so we can just execute this code and
we've inserted another two records into this
table we can go to our select script and select top thousand rows
from the student table and we should see that our table has
been populated okay guys thank you so much for staying
along with me on this tutorial i hope you've learnt a lot how to insert
data into a table in sql server management
studio using the graphical user interface
and also writing t sql code as well have a good day and
see you in my next video bye