Creating a table using Transact-SQL commands

- Let's recreate the same Departments table we just made using TSQL commands. To do that I'm going to come up to the New Query window and open that up. The first thing I'm going to do is type in Use, to make sure I'm using the correct database, in this case it's going to be the HPlusActive database, and I saw on the Intellisense so I just press the Tab to enter that in and then we'll go down to the next line. And then I also want to drop the existing Department store label we created in the last movie, which is this one right there. I don't want that to interfere with what we're about to do by creating a new table. So I'm going to type in here Drop Table, and this time instead of typing in the name of the table, one of the other ways that we can interact with this SQL window here is to just drag values in.

So I'm going to click on the Departments table here and just drag and drop it right there and that will add it to the statement. Let's go ahead and execute this and it's going to say it's completed successfully. And if I refresh my table view here by right clicking, you'll see that that Departments table is gone. So let's go ahead and highlight both of these and delete it and we'll start creating our new table with the keywords Create Table. Then we're going to type in the name of the table, which is Departments, and then open up a parenteheses. I'll come down to the next line and press Tab and we'll fill in the details for the first column, which is going to be DepartmentID, and the datatype will be a character of three.

So DepartmentID, and I'm going to press Tab a couple of times here. I type in char and then three inside of parentheses. Tab a few more times and then I'm going to type in Not Null followed by a comma. The reason that I'm pressing Tab a few times is that as I add additional columns to this table statement here, all of the data attributes are going to line up nicely so it will be really easy to read. I'm going to come down to the next line and we're going to enter in the next column which is the DepartmentName with the datatype of char 50, and that's also going to be Not Null.

Come on down to the third line, we're going to enter in DepartmentHead, and the entity type is integer. The fourth column is going to be our BuildingNumber, and that uses the small int datatype. And finally, the last line is ModificationDate which uses the timestamp datatype. So normally that would be the end of our Create Table Statement, but I want to add one more line on here, and that's to identify that the DepartmentID is going to be used as the primary key for this table. So in order to do that I'm going to make sure that I have a comma here after the last statement of Not Null, we'll come to the next line here and I'm going to type in the word Constraint.

And then the name of the Constraint, which I'm going to call PK_Departments. Then we'll say that it's going to be a primary key constraint that uses a clustered index. And then finally, inside of parentheses I'm going to type in the name of the field that will act as the primary key constraint, which is the DepartmentID. Then finally, I'll come on down to the next line and then I'll type in the closing parentheses and you'll notice that the code hinting also helps me with parentheses. You see that this one is highlighted gray right now? And that indicates that it matches with this one up here at the top, these are the matched pair parentheses that I'm closing.

Go ahead and finish that with a semi-colon and then we'll go ahead and execute this statement. It says that commands were completed successfully, and again, if I refresh my table list, I should see the Departments table right there. And the last thing that I want to do is replace the record that was in the original table that we just deleted. So in order to do that I'm going to come down a couple more lines, and I'm going to type in Insert Into the name of the table, which is Departments, and then a list of the fields that I want to insert. In this case it's going to be DepartmentID, the DepartmentName, the DepartmentHead, and the BuildingNumber.

On the next line I'll type in the keyword Values and a list of the values that I want to insert, which is going to be MKT, for the DepartmentID, and since it's text I need to wrap that in single quotes. The DepartmentName is Marketing, also in single quotes. The DepartmentHead is an integer value that points to the Employees table. And the BuildingNumber is just a small int, so I'm going to type in number three for that. Go ahead and close that off, a closing semi-colon to finish the statement. And this time I'm going to highlight these two rows here just to make sure that when I execute it I'm just doing these two lines, not all of these lines.

So with those two lines highlighted I'll execute it. It says one row was affected. And if I come into my table of Departments, right click on it and say, Select Top 1000 rows, we can see that those values have been filled in, including the ModificationDate to show that I was just adding those values. So that's our basic table structure. And in the next movie we'll finish it up by adding a constraint on the BuildingNumber field, and an Index on the DepartmentName.