How To Perform an Insert in CockroachDB

Introduction

If you’re just getting started with CockroachDB, it’s important to familiarize yourself with all the basic operations. One common task that you’ll find yourself performing again and again is the insert operation. Fortunately, inserting a record is a quick and easy task, especially if you have some knowledge of SQL. In this article, we’ll provide step-by-step instructions for inserting a record in CockroachDB.

Prerequisites

Before we begin looking at the SQL statements needed to insert a record, let’s take a moment to discuss the system requirements for this task. There are a few important prerequisites to consider:

• You’ll need to make sure that CockroachDB is properly installed and configured beforehand.

• You’ll also need to ensure that the CockroachDB service is running in the background. To check whether the service is running, use the following command:

• Open another terminal and use the following command to start the CockroachDB SQL client.

• It’s helpful to have some basic knowledge of SQL syntax in order to follow along with the examples presented in this article.

Creating The Sample Database

Let’s begin by creating a sample user and database that we’ll use in this tutorial. You can use the following statements to create the raizel user and testdatabase database.

For the purposes of this tutorial, we’ll give the user read and write permission to the database:

Next, we’ll create a sample table using the following statement:

NOTE: Whenever you want to exit the SQL shell, you can simply type \q.

Inserting a Single row In CockroachDB

The SQL statement shown below will insert a single row into a table in a CockroachDB database:

The results you see will look like the following:

To verify that our INSERT statement executed successfully, use the statement shown below:

The results that are returned should look like the following:

Another method of inserting a row into a CockroachDB table looks like this:

NOTE: In the above SQL statement, the table’s column names were omitted. Therefore, you must make sure to list the values that are to be inserted in the declared order of the table columns.

Like we did in the previous example, we can verify that the insert was successful by using the SELECT command. The results should look like the following:

Inserting Multiple Rows into an Existing table in CockroachDB

Now that we’ve learned how to insert a single row, let’s try an example where we insert multiple rows. The SQL statement shown below will insert multiple rows into a table in a CockroachDB database.

A multi-row insert process is faster than multiple single-row INSERT statements:

The output will look like this:

If you’d like to verify that the operation was a success, you can use the same SQL statement SELECT * FROM tblrecords that we used before. The results will look like the following:

Inserting a row using SELECT Statement in CockroachDB

For our next example, we’ll need to create another sample table:

Now we’ll perform an INSERT where values being inserted into one table are being SELECTed from another table:

You can use the SQL statement shown below to verify that the insert process was successful:

The result should look like the following:

Conclusion

If you’re using CockroachDB to store and manage data, it’s important to know how to perform basic operations such as inserting records. As you can see from the examples, performing an insert in CockroachDB is a simple and straightforward task. Using the instructions provided in this article, you’ll have no trouble inserting records in CockroachDB.