Lecture 17-- Add data with an INSERT statement
- [Instructor] New records can be added in to your data tables using the Transact-SQL key word, Insert. To use it, we'll specify the columns that we want to insert data into, and then supply a list of the values. Let's take a look by first exploring the existing content of the Sales.SpecialDeals table, by creating a new query. I'll type in SELECT * FROM Sales.SpecialDeals When I execute the query, I get the results down below, that shows me that I have two records in this table currently.
Now, what I want to do is add in a third item, that'll correspond to a 25% off sale, that'll occur in the month of December. To do this, I'll need to specify a new data point for the special deal ID as number three. We'll need a new deal description, let's go ahead and scroll over here to the right. I want to specify the new start date and end dates for the promotion, the discount percentage, and if I scroll all the way to the right, I'll also want to specify the "Last Edited By", which is the employee that edited this record, as well as the "Last Edited When" columns. Let's come back up here to our query, and I'll Enter down a few lines to give myself some space, and we'll start this statement with the Insert keyword.
We're going to insert a record into the table called Sales.SpecialDeals. I'll open up a parenthese, and come down to the next line. Here is where we're going to specify which columns we're going to be entering in values into. The first column was SpecialDealID, and you'll notice that I can actually use my IntelliSense here now, so I'll go ahead and press the Down Arrow key, and press Enter to fill that in. Type in a comma, the second column is DealDescription, the third column is StartDate, followed by EndDate, then we have the DiscountPercentage, the column called LastEditedBy, and finally, LastEditedWhen.
We'll finish that off with a closing parenthese, and come down to the next line. So, these are the columns that we are going to enter values into. Next, we need to specify those values. I'll type in the keyword Values, and then an opening parenthese. Now, each of these values are going to get wrapped in a single quotation mark, and I just need to fill them in in the same order that my columns are listed up above. So, the value for the Special Deal ID is going to be '3'. The deal description is going to be '25% off in December'.
The start date is going to be '12/1/2017'. The end date is going to be '12/31/2017'. The discount percentage is '25'. "Last Edited By" is an integer value that just specifies the ID number for the employee that's entering this record. I'm just going to use employee ID number '1'. And finally, Last Edited When will be today's date, which for me, is '11/6/2017'.
We'll finish it off with a closing parenthese, and we're done with our statement. Let's go ahead and highlight just these three lines, so we're only executing the Insert statement, not this Select statement up above. I'll press the Execute button, and I get the message that one row is affected. Now, I'll select everything out of the table once again. I'll highlight the first two lines, and execute those. Now we can see that we have the new record number three, let's scroll over here to the right, we can see that it has the deal description, the start and end dates, the discount percentage, as well as the Last Edited By and Last Edited When columns.
So, inserting data with Transact-SQL works similarly to the other queries that we've seen. You'll specify where the inserted data is to go, and what the values are going to be. Remember that each group is just a comma-separated list, and that the column names and values need to be in the same order.
▶️ DTube
▶️ IPFS