If you don't already have SQL Server Management Studio open, please go ahead and open it now.
We have briefly covered the insert statement previously in this course, but there's a few more things
that we need to cover.
I'll just use the customers table and right click.
And we can use the script table insert template.
And I'll just open a new query window and this will be a starting point.
This template is an example of a value list insertion.
So here we have the column list.
And then we have a list of values that are matched up with the columns.
So here we have the customer ID which is the first in the values list.
And whatever value we put in there is going to go into the customer ID column and so forth and so on.
It's important to note that these columns do not have to be in any certain order, as long as the values
are going to match the column that they're going to go into.
Now, if we take a look at our primary key in the customers table, I'll right click and do properties.
We're going to see that the primary key is not an auto increment.
And so we are going to have to supply a correct value.
It has to be unique.
And so what I'm going to do is just right click and select top 1000 rows.
And then if we want to see what the largest customer ID is, we can just order by customer ID and then
that is going to be descending and executing.
That should give us the highest customer ID.
And so our inserts have to be greater than 665.
And so going back to our insert template we'll just plug in some numbers here.
So I'll use 667 for the ID.
And then we will put in Jerry Garcia and our address.
I'm just going to make some stuff up here.
We'll just do a zip code here.
That is a varchar as well.
So we have to put both the zip code and the phone number as strings.
There we go.
And then we can just hit execute.
We got one row affected.
So if we go back and rerun this now Jerry Garcia is on top.
And going back to our SQL query, you do not have to put in a list of all the columns, as long as you
are putting them in the order in which they were created in the table.
So when we use the script templates, it will automatically put them in the order that the fields were
created in the table.
And so we can do another example here.
I'm just going to copy this, and I'll just open a new query window.
And I will just get rid of the column list.
And of course I have to go change these.
And I'm going to put everything up on one line.
So it's kind of easier to see.
So here I could just put some different values here.
And as long as we have all the columns and in the correct order, we can just hit execute and it will
put in the new values.
So we don't have to have the column names in there.
And we'll just go check that.
There's Thomas Covenant.
So for the next example we'll go to the production database.
From previous assignments we made most of these tables with an ID field that was autoincrement.
But we haven't put anything into the staff table, so it's still blank.
So what I want to do is modify the ID field on staff to be autoincrement because we haven't touched
it yet.
So we'll go to identity specification.
We'll make it an Is identity table and then we will save that.
And then I'll just close this and we will access our insert template.
Notice that it does not show the ID field.
And that's because it knows that you do not have to add data to that, because it's going to take care
of it.
So it does not list the ID field.
And we'll just give it a last name here.
And then we have to pick a role ID from our roles table.
So let's just take a quick look.
And one is operator two is repairman.
We will make Mr. James Smith a repairman.
And we can just hit execute.
And going back to our staff query, we have now one staff member, James Smith, who is role ID two,
which is a repairman.
So for our last insert example, let's take a look at our production defect tracking table.
I'm going to right click and go into the design mode.
Now as originally designed the operator comments field will allow you to put in a null value as well
as the supervisor ID, supervisor notes, and supervisor station.
So the initial insert of a record requires all these other fields.
But if you think about our production process, it's the operator that initially enters the record.
And at that time we don't know the repairman or what station they're working at or their notes.
And we also don't know the root cause.
So we're going to make a couple of quick changes to allow nulls for these initially.
And that way we can go ahead and have the operator enter the initial record.
Also, let's go up to the ID field and let's make sure that it is set to identity specification.
I'd set this earlier.
So if yours is not set to this go ahead and make sure his identity is set to yes.
And then we'll just save the table.
Now we need to think about the defect time.
This is a date time data type and we haven't encountered it before in this course.
If you go to the course resources, there's a link to the get date function on the Microsoft site.
And the purpose of this function is that it returns the current database system timestamp as a date
time value.
And so we can use this to get the current date time for our new record.
And a quick way to show you how this works.
I can open a new query window and just type select Get Date.
Notice that the function turns kind of a pink or magenta color, and if I mouse over it, it tells you
that this is a built in function and it returns a date time.
So if I execute this, then we do get the current date and time.
Now it stands to reason that we could just use this for the column value.
But there's actually a better way if we go back to our defect tracking table definition and look at
our defect time, there is a space to put a default value.
And I could just simply put get date here as a function call and then save that.
And so when a record is entered, if a value is not supplied for defect time, then it will make this
function call to get the current date time.
If you're entering a record with a date time in the past, of course, you can just use the insert statement
to put a value in that field.
But this way, no matter what, you will always get a valid value for our defect.
Time.
Now I'll make sure that this is saved, and I'm just going to close these tabs out.
Now let's look at entering a record.
We only have one operator here and they are a repair person.
So we can do just a quick insert statement here.
Insert into staff values.
We don't have to put ID so we will do Ringwald Molly and then operators or ID one.
And then I can just highlight this and hit execute.
And then I'll highlight our select statement.
And we now have an operator and a repairman.
Now I'm going to comment this out here.
And let's go over to our production defect tracking table.
Right click and we'll go to Script Table Insert to New Query window.
And looking at our template we don't need our defect time.
We got to get rid of that comma.
And we're not going to be entering anything except for the operator information and the defect information
the initial record.
So I'll just get rid of those columns and then I'll get rid of the same values.
And so this is our template for our first insert.
So for our operator ID that's going to be number two Molly Ringwald.
So I'll just put a two here.
And then the station ID let's just give it station number one.
We can look at our categories.
Assembly is the first station.
So again we're going to put a one for that.
And then the reason ID will just take a look at our reasons and let's just say it's incorrect Assembly.
That's number two.
And then for our comments, this is a varchar.
Let's just say this is a test entry and I can just hit execute.
Now let's go back and take a look.
And we do have one record.
There's our date and time, our operator ID, station ID, category ID and our comments.
And we can see that NULL was displaced in the rest of the fields.
And we will be using the update statement to update these as this defect moves through the process.
Now let's take a look at how to use the update statement to update existing records.
And for this we're going to go back to our battery Co database.
Because we have a lot of data here we can play around with.
And if you are just practicing queries, you might want to make a backup of a table so you don't mistakenly
update records that shouldn't be updated.
So we can use the select into statement to do that, and we'll just make a backup of the customers table.
I'll hit new query and we will do select star into.
And we're going to give it a new table name.
So let's say customers update exercise.
And this way if you're trying to formulate how to do a query we're not going to mess up existing data.
And so we'll do from customers.
And of course you could put a Where clause here and only get a subset.
We're not going to worry about that.
We'll just hit execute.
And then of course we will refresh.
And here is our table.
And we can look at this table.
And it's got all the records.
So let's practice some updates here.
Let's say that pseudocode here got married and her last name was updated to Miller.
How could we update just this one record?
Now when using an update statement or a delete statement, you do have the possibility of updating a
lot of records that you didn't mean to.
And so I kind of have a philosophy that I use.
I call it select first.
And it's a very simple idea.
Before I update or delete data from a table, I create a Select statement that confines the data to
only the records that I want to affect, just to make sure I'm not going to make a mistake if I only
want to update the record.
156 pseudocode.
Then first I write a Select statement, select star from and I can just drag over this and then where
cust id equals 156.
And then if I execute this then I have constrained the Where clause to show only the record that I care
about.
Then the next part of this is I just turn my select statement into an update statement.
So when we do the update statement, it is simply you do the update keyword and you put the name of
the table that you want to update, and then you put in the new field values.
So we can just do set L name equals miller where the customer ID equals 156.
Now when you're doing this in the same query window and you're selecting this, you need to make sure
that you select all of this.
Because if I just selected this portion it would update every customer's last name to Miller, which
is of course why we do a backup.
I will highlight this hit execute.
It will tell me how many rows were affected.
And then of course I could just do a quick select statement again where cust id equals 156 and we select
this and we can see that sue du code is now Sue Miller.
So the update statement is pretty easy, but you almost always use some sort of Where clause.
And you could use any of the clauses that we covered before.
You could use relational operators, Boolean operators.
You can have multiple where statements whatever you want, but it's important to use the select first
method so that you're not updating data that you don't want to.
Since we do have another backup table, let me just show you what would happen if you don't take care
and use a Where clause to constrain the data that you're updating.
So here of course, is our customer backup.
And if I were just to say update and then customer backup set L name equal Miller.
If I execute this basically it is going to set everybody's last name to Miller.
And now I've just basically destroyed my data integrity.
And so that is why you always use a Where clause to constrain your updates and your deletes.
So let's look at another example where we're using the select first philosophy to make sure that we
have the right records.
Before we do an update, let's say that we want to give a raise to all of our employees that work in
Department 300.
And make less than $75,000 a year.
So I'll open a new query window, and we will write a Select statement to select all employees who are
in Department 300, and their salary is less than 75,000.
And we'll execute this and make sure that our data is correct.
Now notice that I did a select star which selects all columns.
And then I did a comma.
And I added a another column which is going to be salary times 1.1 which is 10% higher.
And we're just calling it raise.
So you can see the before and after values that our employees will have after the race.
So now that I have my select statement that constrains my results to exactly the records that I want
to update, then we can convert that into an update statement.
Here we are updating the employees table and we're setting our salary equal to the current salary times
110%, where our department ID is 300 and our salary is 7500.
And so looking at Jennifer Litton, her salary will go from 58,009 30 to 64 823.
If we execute this there should be seven rows that are updated.
We'll hit execute.
It did update those seven rows.
And then if we look at our employees and we can just go find Jennifer very quickly where first name
like and let's just try this like Jen there's Jennifer Litton and her salary is now $64,823.
So she's got her raise.
Now let's go back to our production defect tracking.
And let's say now we want to update this record with repair information.
So we want to enter repairman ID, station ID and repair notes.
Those are the only columns that we want to update.
So the key field our ID is a primary key field.
We can use that to identify this record.
Now yes of course there's only one record in here but we can still use that.
So let's go to a new query window select star from Production Defect Tracking.
And we can hit execute just to see what we have.
And we'll go ahead and put our Where clause just so that we are constraining the records.
And then we can write our update statement.
So we will just say update production defect tracking.
And then we'll use our set command.
I can just pull those out here.
So we'll do repairman ID equals.
We'll have to go back to our staff.
So our repairman is James Smith.
He is ID number one.
So we'll say set repairman ID to number one.
And then we use a comma.
And then our station that is also going to be a one because we're at the very front of the line we use
another comma.
And then our repair notes.
We're just going to do a Varchar and just put in assembly defect test entry.
And then we would do where ID equals one.
And if we do an update here and then we can do our Select statement.
And you can see that it did update our repair stations.
Now let's go take a look at how to use the delete statement.
We'll go back to Batterico.
And we're going to go with our customers update exercise.
So we're not actually pulling people out of the course database.
And we'll just do a select star from table to get a look at the records.
And let's say that we just want to delete Sue Miller.
So again we have an ID field that uniquely identifies this record.
If we want to delete Sue Miller, we could use the customer ID and using the select first we will do
select star from customer update where customer ID equals 156.
And we will make sure that it constrains it to only the records that we want to get rid of.
And then we just say delete from customers update exercise where customer ID equals 156.
And then highlight that and hit delete.
It'll tell you how many rows are affected.
If we go back and highlight our select statement, you'll see that that record has been removed.
Now looking back at the table, you may ask, well, what if instead of using the customer ID, we just
said where the first name equals Sam and the last name equals over?
Well, it is possible to have two people with the same name.
And if you do that, you run the risk of deleting the wrong person.
So it's always good to do update and delete statements from a primary key field just to ensure that
you have exactly the right record.
Now let's go ahead and do one more example with delete.
And one thing if I'm doing SQL queries and I do a delete statement, I'm always concerned that I'm mistakenly
going to have it highlighted.
And if I were to just highlight this and hit execute, it would delete everything in the table.
So this is called a block comment.
So it's a forward slash and then a star.
And then when you're done with the block you can have as many lines as you want.
Then you do star slash.
And then you can see that it has commented that out.
Now I could still highlight this and hit execute and it would still do the delete, but this at least
gives me a visual cue to kind of stay away from it.
And if I were to just hit execute, which would execute all SQL statements in this query window, I'm
at least somewhat safe.
So let's say that we want to delete all records from this table where they have an invalid zip code.
Let's say that those are invalid customers, so we will do our select first select star from.
And we're doing our update table just to be safe.
And we'll say where zip.
Not like it's just going to be zero through nine.
And if you remember us zip codes are five numbers and they don't include letters or anything else.
So this will find all zips that don't exclusively include five numbers.
So we will execute that.
And we can see that these results are correct.
We've got one here.
But that's only four characters.
Those are only four characters to the rest have spaces and dashes and stuff like that.
So now we know that we've constrained the result set to be only the records that we want, then we can
convert this into a delete statement.
And this is 13 rows.
I'll hit execute.
It affected 13 rows.
And now I can change it back into a select and execute that.
And we don't have any invalid zip codes.
So we were successful.
So next up we're going to be doing an assignment.
You'll be doing inserts updates and deletes.
And just remember for your script table there is also an update template that shows you how you can
do multiple columns and where to put your search conditions.
There is also the delete template that shows you a delete statement for your Where condition.
So take a look at these as you tackle the next assignment and I'll see you there.