Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The insert into statement is used to add a row or rows to a table. We'll select the scratch database here for this lesson. And I'm going to start by creating a temporary table. I'll just call it test. And we'll give it a couple of columns here. A INT, b TEXT and c TEXT. And now we'll insert a row into the table. INSERT INTO. So, I'm typing all of these.
Statements and I'm going to execute them all at once. So we're going to insert into test values one this right here and then we'll use a select statement to view what's in the table. Which should be one row. Now when I press the go button it will create the table and insert a row into the table. And then display it with the select. And there is the row that we created. So the insert into statement resulted in a new row in the table. And that's this statement right here, the second one.
The values clause is used to list the data values for the new row. So it says values and then in parentheses here it has those values for the new row, One, this one right here, and there they are in the table. Now you don't need to specify all of the columns in your values clause. You may optionally list the columns you want to provide in your values data. So, I'm just going to replace these two lines. We'll leave the select statement there, and I'll insert another row.
And here I'm going to list a couple of columns, just the B column and the C column. And just give values for that for those columns that and over there. So, this is a different form of an insert statement where before we just had values and we listed all three columns. In this one we're specifying. Two columns before the keyword values b and c and just the values for those two columns. So when I press go we get null and that end over there.
So the A column which is the int column has a null because there's nothing specified for it. So notice the result for the a column. This is null. Null is a state it's not actually a value, it's different and it's distinct from zero or an empty string. Null is a special state that represents no value. We'll talk more about null later on in this chapter. There's one more form of the insert into statement that's worth knowing about. You can use the results of a select statement in place of the values clause.
So, for example, I can say insert into test, like this and I can list some columns, a, b and c. And instead of the values clause I can say select ID name and description from item. Like that. And so this will take all of the rows from item. And take the id, name and descriptions columns and it will insert them into the test table.
And then we'll leave that select there so we can see the results. And here we have four rows from the item table. In the scratch database and their id name and description columns are mapped into the a b and c columns in the test table and select star from test it shows those results. So the results of the select statement are being used as the value. For the insert into statement.
This is a valuable technique for storing temporary or persistent results from a query. The insert into statement is very simple. There are several forms that allow you to specify what columns to populate and even to add rows from the results of a query. I'm going to leave this test table alone for now and the next lesson I'll show you how to update rows.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 102115 Viewers
61 Video lessons · 88841 Viewers
71 Video lessons · 72651 Viewers
56 Video lessons · 104289 Viewers
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.
Your file was successfully uploaded.