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
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
<div id="pastingspan1">A: This usually means that you have the Apple version of the Apache Web Server already running.</div> <div> </div> <div id="pastingspan1"></div> <div id="pastingspan1">Apple provides a version of the Apache Web Server with OS X. I chose to use XAMPP because it includes PHP and the MySQL server all in one package.</div> <div> </div> <div id="pastingspan1"></div> <div id="pastingspan1">You cannot install XAMPP with Apache already running on your Mac. If you see "It works!" when you open your browser and type "localhost" into the address bar, this means that the Apple version of Apache is already running. You cannot run two instances of a web server at once, so the Apple Apache must be stopped before you can install XAMPP.</div> <div> </div> <div id="pastingspan1"></div> <div id="pastingspan1">In OSX 10.7 and earlier, you could start and stop the Apache Web Server in Control Panel, by choosing "Sharing" and then unchecking the "Web Sharing" checkbox. As of 10.8 this option is no longer in the control panel.</div> <div> </div> <div id="pastingspan1"></div> <div id="pastingspan1">To disable Apple's version of Apache in current versions of OS X, open a Terminal window and type this command:</div> <div> </div> <div id="pastingspan1"></div> <blockquote style="margin: 0 0 0 40px; border: none; padding: 0px;"> <div id="pastingspan1">sudo apachectl stop</div> <div> </div> </blockquote> <div id="pastingspan1"></div> <div id="pastingspan1">You will need to type your system password when prompted, then you should be able to start the XAMPP version of Apache.</div>
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
<p>A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this: </p> <blockquote style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"> <p> </p><ol> <li>Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL</li> <li>Control-click on the SQL folder and select "Get Info" from the context menu. </li> <li>Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window. </li> <li>Now repeat the process for the three *.db files inside the folder. </li> </ol> <p> </p> </blockquote>