You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing firstname.lastname@example.org.
We are experiencing playback issues from our video hosting provider. Please check back shortly.
3:37Creating a Table
3:05Inserting Data into a Table
2:30Selecting Data from a Table
3:25Filtering Select Results
3:34Conditional Where Clause
3:21Updating Records in a Table
2:57Deleting Records from a Table
Take topic challenge
Inserting Data into a Table
The SQL "INSERT" statement allows you to add new rows to a table in a database. This lesson shows how to write an INSERT statement using a Named Query.
Video recorded using: Ignition 8.0
Transcript(open in window)
[00:00] In this lesson, we're go take a look at how to insert data into a table in the database. I'm going to start by creating a new named query and I'm going to call it Insert Data and then go ahead and click on the create named query button. Here in my named query, I want to start by setting my database connection to internal DB and because we are going to insert new data into the database and update that table, we want to change the query type to update query. My query will not have any parameters, so if I wanted to, I can get rid of both of these parameters. I can then paste my query down below. You can see with an insert query, we start out with the command insert into and then list the table that we want to insert data into. In this case, inventory.
[01:03] Next, in parentheses, I list out column names. These are the columns that I'm providing data for that will be inserted as a new row. Finally, we use the keyword values and then in parentheses again, we list the values that we want to insert into the specified columns. The values do need to be listed in the order of the columns that we set earlier, so in this case, I have a name of workstations, a location of marketing, and a quantity of 15. I can go over to my testing tab and try executing this query. Because I'm inserting data, I'm not expecting it to give me data back, but what it does return is the number of rows that were affected by my query. In this case, we see a result set of one, meaning I inserted one new row into my table. I want to go back and take a look at the query and take a note of two things.
[02:04] First, it is possible to insert dynamic values by creating parameters for each value that you want to make dynamic. So in this case, I could make a parameter for name, a parameter for location, and a parameter for quantity. I can then replace the hard-coded values with those parameters instead. This would allow me to insert new values when I run the query. The second thing that I wanted to note is that it is possible to insert multiple rows of data with a single insert query. I'm going to paste some additional values in here and you can see that all we need to do is simply list out additional sets of values separated by commas. If I go to my testing tab and try to execute this query, you can see that my result set is now seven, indicating that I inserted seven new rows into my table.