Creating a Table


Learn how to create a table in an SQL database. Tables are primarily used to store information in the database, we we'll need to create a table before we can continue demonstrating other common commands. This lesson features an internal database connection. Learn more about these types of database connections can be found in the links below:

SQLite Connnection IU Video
SQLite Connection User Manual Page

Video recorded using: Ignition 8.0

[00:00] The purpose of this topic series is to interact with data within our database using a query. Using this data in components within an ignition project will be discussed in a later topic. The purpose of this lesson is to go over how to create a table in a database. We can create a table in our database using a specific type of query. Now we can run this query from our database query browser in the tools menu, or we can go to the database management software and run our query there, but I'm going to be running all of the queries in this topic series using a named query. I am going to create a new named query by right-clicking and selecting new query, and I'm going to name this query create table.

[01:00] I'll then go ahead and click the create button and navigate over to the authoring tab. The first thing that I want to do is set the database connection that I'm going to use for this named query, so I'll go into the database connection dropdown and select my database connection InternalDB. I do want to point out that the database that I'm using here is the built-in SQLite database that you can set up. For more information on setting up database connections, you can see other videos within Inductive University or go to our user manual. Next I need to specify the type of query that I want to run. In this case I'm not going to be pulling data out of the database but I am going to be creating a new table, which is similar to inserting or updating the database. So I'm going to change my query type to update query. In this particular instance, I don't need any parameters, so I can actually delete the two parameters that I have right now.

[02:00] Finally, I am going to paste my query into the query area here. To create a table in the database, we start out with the command create table, followed by the name of the table that we want to create. In this case, my table will be called inventory. Next, within parentheses, we list out the names of the columns that we want to create within our table, as well as their data types. So you can see I have three columns here, name, location, and quantity, with data types of text, text, and integer, respectively. I do want to note that this syntax may differ depending on what type of database you're using. Keep in mind we're using the internal SQLite database connection. Your table can have any number of columns and the columns can have a number of different data types, again, the data type names differing depending on what type of database you're using.

[03:02] With our query entered, we can now run our query by simply going to the testing tab and hitting the execute query button. We weren't expecting any data, so I wouldn't expect to see anything in this results list here, but if I go back to the authoring tab, we should see that now, in my table browser, I have the inventory table listed and if we expand it, I can see the three columns that I added to that table, name, location, and quantity.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.


Share this video