LibreOffice Base SQL: Inserting Data Records

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 Tables

Altering Tables

Table Constraints

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.

Advertisements

2 comments on “LibreOffice Base SQL: Inserting Data Records

  1. […] In my last post, we created the “TeamMember” table and inserted several records in it. If you haven’t […]

  2. sergburk says:

    good information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s