Join Bill Weinman for an in-depth discussion in this video Inserting rows into a table, part of SQL Essential Training.
The insert-into statement is used to add a row or rows to a table. Here we're going to use the test.db database. I'm going to go ahead and create a test table in here, say create table test. We'll have an integer and a couple of text columns. And then we'll do a select. And you'll see that the table is empty so there'll be no result. Two queries performed, but no result. So, that's an empty table at this point.
So, now we can insert something into it. Insert into test and the keyword values one comma this right here. So the text columns have single quotes around the values. And when I insert this row into the table and press Go, you'll see that our select statement now returns a row from the test table.
So the table has been created and some values have been inserted into it. So the insert into statement has resulted in a new row in the table and the values clause is used to list the data values for the new row. You don't need to specify all the columns in your values clause. You may optionally list the columns that you want to provide, like this. So I can say insert into test and I can put parentheses here and I can list the columns that I want to provide values for. So I can say, b ,c, and I don't need this first value there.
And I just need values here. So I'll just say that and over there. And now we have another insert statement, but this one only specifies the B and the C columns because I've listed them before the keyword values. And I've listed the values for these b and c columns after the key word values and all those lists go in parentheses. So when I press Go, you'll see we'll get that and over there! And we'll get a null in the a column. So notice the a column has this null value. This is different and distinct from zero, or from an empty string.
Null is a special value in SQL that means there is no value here. So there's one more form of the insert into statement that's worth knowing about. You can use the results of a select statement, called a sub select, to add rows to a table. And we can do this, like this. Insert into test. And instead of this whole values clause I can say select ID comma name comma description from the item table.
And I can even before the select list the columns I want these to go into. I can say a b c. Or I could even do them in a different order if I wanted to. And if that works for the result that I'm expecting from the select statement. So the select statement is going to give us several rows to insert, and we'll get several new rows in our table. So I'm going to press Go, and now you see our table has four new rows at the end of it. And these are the results from the select query. This is a valuable technique for storing temporary or persistent results from a query, and you will see it a lot in SQL.
So the insert into statement is used to add row's to a table, there are several forms allowing you to specify what columns to populate. And even to have rows from the results of a query. I'm going to leave this test table alone for now, and we're going use it again in the next lesson to show you how to delete rows.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views