Tip:
Highlight text to annotate it
X
I've made a copy of
odb29 in and calling this one odb
31 query insert. We're going to create
the employees phone number table we designed in explained in the previous
video
and populated with the data we discussed in previous video
let's create a new table. We're going to click and tables in a database window
and create table in design view
are first
field it's going to be employee-ID we need this
field-type to be integer. Our next field is going to be the phone number
and we need this field type
to match the field type from the employees table
because we're going to copy that information into this field
and that has the type text varchar
of fourteen. Our next field is description
varchar 100 is fine
and notes, text varchar 100 is fine for that too.
Let's save this we're going to give this the name
tbl_Emp
-Phn for employee phone numbers clicking OK to save it...
shows us we did not and a primary key
I'm going to cancel out here. We said
in the previous video you that we were going to make our single primary key
the employee ID and the phone number together
so I selected them, I'm right clicking
and assigning them the primary key
let's save this table
and we can close window. we have the new
empty table created now we want to add the data
we talked about in the previous video let's bring up that calc file to remind
us
of what we were looking at and we're going to start with this place
up north that Stephen Hill has we already know
a couple of ways to get this information in the table we can
type it into the table directly we can type it in
using a form or we can load it in
from a Cal file now we're going to learn another way
to insert records into a table and that is with an
insert query we can't run
an insert query the same way we run our standard select queries
that method only works to view the data if we want to
insert a record we need to go into tools
SQL... and this
opens up an SQL command window
I have the query saved in a text-file
and here we see we're going to insert into
our employee phone number table these fields employee ID
phone number and description we're going to place in these values
mean we're going to please 17 into employee ID
this phone number into phone number and place up north
into description you notice that I don't have notes
in the fields that field was not required
and if I don't included in my insert LibreOffce will place NULLs
in that field Let's run this query
copy
and paste
let's execute this we see that it
executed successfully to see that we
inserted that field properly
We will want to run a select we can do is select from the
command window but will need checked this little checkbox
show output of SELECT statements
clicking execute shows us that
it was successfully inserted in over here on the right we see
two commas next to each other showing us that we've got NULLs in that last field
this output is a little difficult to read
in easier way to see that we inserted the record into the table is to just
open up the employee phone number table here we have it looking a little nicer
to insert our next record
I've get another insert going back into tools
SQL and I'm going to click
execute to insert that record into the table
it also went in without any problems
now if I try to run this again... we see that we
get a violation of the unique key constraint
duplicate value that's because we're trying to insert the same record again
with the same employee ID number and the same phone number
if we change going to those two it wouldn't be a problem
bringing back our window I will click Refresh
we see that we did insert the
brothers house into our phone people let's bring back that Calc file
for a minute we see that the place up north
has a single record the brothers house has a single record
the cell phone 2 has got six records in it
but we're only allowed to insert a single record
at a time how we're going to do this is
we're going to create 6
individual inserts and separate them
with semi colons I'm going to select all by
using control+a control+c to copy
and control+v to paste
now I'm going to execute this
and we see: command successfully executed
so it appears that only one command was successfully
executed but we really did 6 individual
inserts let's do the
same thing here with home phone
run that
command successfully executed let's take a quick look at our table to make sure
everything has been inserted properly
still we only see the two records we need to
refresh the table and there we see we've got
our home phone cell phone 2 and it looks like we missed
the cell phone Let's add that in... I'm going to
select all copy SQL
and insert that - that insert seems to have gone well
let's bring back our table
and refresh it - it looks like we've got everything in here now we've got our
cell phone, home
phone, cellphone 2, our brothers house...
so we're all ready now to
do our last query which is going to be too
pull the data from our employees table
and place it on to this new
phone numbers table I'm going into
tools SQL and
here is the query that we're going to use we're going to insert into
the employee phone numbers the same as before
at the top but down below instead of having
values we have a select we're going to
select the employee ID and the phone number
from our employees table but this third field
has the single quotes so we're putting in this default value
of work phone into our description Let's select this
place it into our command window
executing it shows that that successfully executed
and looking at our table refreshing that
shows us that the work phone was
successfully inserted into our phone table
I will put all of these insert queries
out on the website in case you want to download them - and one last thing we
need to do
is go in to the Employees table and edit that
we can now delete the phone number
off of our employees table saving the table
closing the window
and saving our database all
all cool all
all