SQL Fundamentals
Add Data to a Table with SQL Insert
Tables in databases are not worth much value if they do not actually hold data. In this lesson we will use the insert statement to add rows of data to our table. We'll break down each component of the statement and review what is required for a successful insert.
Instructor: [00:00] As you can imagine, when a table is created, it contains no data from the start. The first step in making your database useful is by adding data. We'll do this by using the insert command.
[00:10] We'll say, insert into users (create date, user handle, last name and first name). Values, we'll say, 2018, June 6th. We'll paste in a new GUID. We'll say, Clark and Tyler. Close it off with a semicolon. Users is the name of the table we're inserting data into. This first set of parentheses shows the columns that the data will match up with.
[00:37] One thing to take notice of is the order in which we define the columns. The data listed below in the second set of parentheses will insert according to the order these columns are defined in.
[00:49] When inserting data, there are two required components to work. First is the table the data is going into. Second is the actual data. We don't actually need to define the order of columns here.
[01:00] As we can see here, we did not provide the corresponding column names or data it needs to match up with. This will insert into the table in the order the columns within the table were created by. It is generally accepted that listing your columns out is best practice because it's more explicit.
[01:17] Keep in mind that our desired inserted data needs to match up with the defined column types. For example, if we insert into our table with only two values, we're going to get a type issue because create date is a type date, and we're giving it a string of Jones.
[01:33] If we were to fix this problem, changing Jones to a date, we'd still get an error because now Debbie, the string, does not match up with a GUID type.
[01:44] We also don't need to provide predefined values. Most SQL databases have built-in functions that can generate values for us. Here, we're inserting a new row into users with just the created column. We're using a built-in function in Postgres called now. The now function returns a date type of today's date.
[02:05] Finally, we have the ability to insert more than one row of data at a time. All we need to do is comma separate new sets of data. Here, we're inserting three new rows of data into our users table. Because we're only specifying create date and passing the now to each set of data, it will provide null values for user handle, first name, and last name.
[02:26] Of course, if we wanted to add more data to each row, we'd just need to define the columns here and then provide the data here.
[02:34] You might be wondering if there's a way to bulk insert data into a table -- for example, from a csv. The answer is yes. However, it's a little too advanced for this course. I recommend looking into that if you're curious. For Postgres, it involves the copy command.