In most cases you will never use SQL to insert data into Libreoffice Base tables as this is mostly done through forms. However, it helps to know how the SQL works for those occasions when you get error messages when trying to input data through a form, and there are also times when you just need a small table with a few records for selection in a form. Creating a form in the latter case might take more time than just entering the data through SQL. Today, I am going to show you the basics of inserting records into a table.
Our Database Table
For our purposes today, we will use a very simple team member table for our data input. Create a new database in LibreOffice Base, then Tools > SQL. Type in, or copy and paste the following SQL into the SQL input dialog.
CREATE TABLE "TeamMember" ( "ID" INT PRIMARY KEY, "FirstName" VARCHAR(25) NOT NULL, "LastName" VARCHAR(25) NOT NULL, "DOB" DATE, CONSTRAINT FNAME_NOSPACES_CK CHECK(TRIM(BOTH FROM "FirstName") <> ''), CONSTRAINT LNAME_NOSPACES_CK CHECK(TRIM(BOTH FROM "LastName") <> '') );
This table has four fields. Three of the fields are required: “ID”, “FirstName”, and “LastName”. The field “DOB” for the date of birth is not required. We create constraints for the name fields to prevent them from being left blank or filled with spaces. For more details on creating tables, see my articles on creating tables:
Creating A Record
Once we have a table, we can start filling it with records. As with many things, we have multiple ways to insert a record into a table using SQL. The template is
INSERT INTO “TableName” (“FieldName”,...) VALUES (DataValues,...);
This is the complete structure of the INSERT command. I will show variations on this command by inserting records into our table. First, let’s look at a complete SQL INSERT command in action.
INSERT INTO "TeamMember" ("ID", "FirstName", "LastName", "DOB") VALUES (1, 'Johnny', 'Walker', '1986-10-23');
Here we name each of the fields in the order they were created and then values to match. Note that the order of the values must match the order of the fields. Also, when entering dates the format is Year-Month-Day. The order of the fields does not matter, only that the values match the field types in the same order.
INSERT INTO “TeamMember” (“DOB”, “LastName”, “FirstName”, “ID”) VALUES ('2001-3-2', 'Smith', 'Johnny', 2);
We also don’t have to include all the fields, but we do have to include all required fields, If we don’t, we will get an SQL error. The required fields in our table are “ID” (PRIMARY KEY), “FirstName” (NOT NULL), and “LastName” (NOT NULL).
INSERT INTO “TeamMember” (“ID”, “FirstName”, “LastName”) VALUES (3, 'Sandy', 'Walker');
And we can still change the order of the fields as long as the values match.
INSERT INTO “TeamMember” (“LastName”, “ID”, “FirstName”) VALUES ('Smith', 4, 'Sandy');
Finally, we don’t have to name the fields at all, as long as we enter the values in the order they were created and we include values for all of them.
INSERT INTO “TeamMember” VALUES (5, 'Henry', 'Walker', '1970-5-10');
This may seem like a great shortcut, but for tables with more fields, you risk making a mistake. The best choice is to list your fields, and then you can make a visual check that your values are matching up.
That is the basics of inserting records into a table. Next, we will cover changing and deleting records using this same table. In the meantime, you can practice by adding more records to the table.