Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
Selecting information is all very well, but after a while we are going to need to start inserting some information into those databases. Now of course if you have SQL Server Management Studio you can do the kind of cheap and nasty way of doing it, which is right-click one of these tables, select the Edit Top 200 Rows, and either start clicking and selecting in here to change things, or you can scroll right down to the bottom and where the asterisk row is just start typing. But this is not really a very scalable way of doing some inserts.
It's really just there for emergencies. We need to understand how to construct SQL statements to do this. Having said that, it is very useful to often look at this this way to familiarize yourself with what data you are about to enter. So I'm going to create a new query and I'll be using AdventureWorksLT, as we have been all along. I'm going to insert into the ProductCategory table here, simply because it's quite a small one. Again before I start writing the insert statement let me take a quick look at what I have here, just selecting from it.
We have ProductCategoryID, ParentCategoryID, Name, then a rowguid and a ModifiedDate. Well even just looking at this I might have the idea that you know some of this is going to be automatically generated. If I want to double check that I could right-click this table and select Design, because if I wasn't a person who had created it I'll certainly want to take a look. So ProductCategoryID is declared as the primary key. It's an integer and if I scan the properties of it I can see that it is set up to be an identity automatically incrementing, so I shouldn't be the person inserting this value.
I should probably be the person inserting the ParentCategoryID. Yeah, looks like the case. It's not an identity. I'll be the person inserting name. And then we get to rowguid. Well, this is a globally unique identifier and it's set up with a default value of the newid function, which means I shouldn't insert this either and nor should I do the modified date which is getdate. So it looks like I'm only inserting two things, the ParentProductCategoryID and the Name. All right, well back to our insert statement. Well I haven't started it yet.
In fact it's not the word INSERT. It's actually the phrase INSERT INTO and now we're going to use the name of the table. We are inserting into SalesLT. ProductCategory. Well then what? Well I have to have a way of saying I only want to insert those two particular columns, which was ParentProductCategoryID and Name. The way I do it is open parenthesis and I type the names of the columns that I'm wanting to insert. Then I use the word VALUES, then I have another parenthesis where I enter in, in the order that I declared them, the values that I want to insert.
In my case I'm going to insert the value of 1 for the ParentProductCategoryID and I'm going to insert the phrase Hybrid Bikes. And if you don't supply the column names for the columns that you're going to enter, the SQL Server will expect that you're going to give it the values of everything of every column, but we don't need every column. So I'm going to go ahead and execute that and see what happens. We hit it and we see one row affected.
It doesn't actually tell us what, but we can get to that a little later on. This is an INSERT statement. It just says yes it worked. If I want to prove that I could just very quickly right-click, say Select Top 1000 Rows, and come down right to the bottom and there I can see Hybrid Bikes. It has a guid that has been generated. It has a modified date that's been generated. I have a product category ID. I've been playing around a little bit with inserting stuff, so I can see that I'm missing a few numbers here. But that's okay.
It's looking good. Going back to this, you can also, if you want to insert multiple rows at the same time, all I could do here is put a comma and then another open parenthesis and say perhaps here I'm putting in something for Girls Bikes. Now here is a question. What do I do if I want to put in an spostrophe, because if I just type it what's going to happen is it's going to close my string here, my little text thing. Well the answer is this. If you want to have the single apostrophe and you have to use the single quote, you just use 2. So this should allow me to insert another entry here.
Now here interestingly we're going to have a problem because there is a constraint on this field that means it should only have one product category name of the same name. We've already inserted Hybrid Bikes, so let's see what happens if we try and insert this again. I had Execute and unfortunately gives me a violation of a UNIQUE KEY constraint. Cannot insert a duplicate key in object ProductCategory. Okay, that's fine. Let's say that was an accident. Anyway what I wanted was in this case this should have been Boys Bikes. I'm going to use the two single quotes as well.
That's not double quotes. It's two single quotes and I'm going to try and insert that. There we go, two rows affected, and if I right-click and do another select it looks pretty good. We have 52, 54 and 55 going on here. Although it does look like I accidentally put some wrong data in the ParentProductCategoryID, all bikes should have a ParentProductCategory of one. Oh well. So I'll have to see how to update that in just a few minutes.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.