Deeper dive in creating data with SQL/ODBC in OpenEdge

Creating data with SQL and ODBC in OpenEdge

INSERT statement is used for creating data in OpenEdge using SQL and ODBC. SELECT INTO can also be used to create data. This article does a deeper dive on the INSERT statement. We’ll cover the use of sequences, populating default values and inserting with JOINs. We’ll cover inserting data into primary keys and the impact of using and not using sequences. Let’s start.

Creating data with SQL and ODBC in OpenEdge

Inserting data into an OpenEdge table with SQL

Let’s take an example of a simple table in OpenEdge and creating data in it.

Then, we can insert data by providing information for each field in the table like this:

We can also insert data by providing information for all fields without requiring to specify all fields, like this:

Then, let’s enter data into required fields. Fields that are not specified either stay NULL or get default data. Let’s take this example:

Note, in the above example, gender defaults to M and dob is NULL. Let’s confirm that:

NOTE: Do not use READ UNCOMMITTED isolation to use INSERT statement; it won’t work. Use either READ COMMITTED, REPEATABLE READ or SERIALIZE isolation level.

Review documentation of INSERT statement from OpenEdge documentation.

Inserting data with sequences

OpenEdge supports the use of sequences. Note that sequences cannot be used in READ UNCOMMITTED isolation level. We can enter records with the next sequence number. Let’s take an example of creating a sequence:

Sequence’s initial value is 3. NEXTVAL returns 4. You can choose to start the sequence with 0. In that case, NEXTVAL returns 1.

Now, let’s insert data using this sequence.

Notice that ID 4 gets inserted, which comes from sequence’s next value. Gender field does not have a default. Instead, gender gets NULL explicitly.

Caution when inserting IDs with sequence and manually

Insert primary key data using sequences or manually. Try to avoid using both methods. Let’s take an example:

  • The first insert uses a manual ID of 5. Insert succeeds. At this point, sequence’s current value is 4
  • Then, second insert uses sequence’s nextval for ID. That’s 5. Since an ID of 5 is already inserted, the insert fails due to violation of primary key. However, sequence’s value will be incremented
  • Then, third insert uses sequence’s nextval for ID and that’s 6. So, insert succeeds

Insert data using SELECT

Let’s add 10 customers from CUSTOMER table need to be added to EXAMPLE1 table. How would we do that? Here’s a way:

The above statement selects a new sequence number for each of the 10 rows and splits contact field into firstname and lastname. That data is then inserted into example1.

Insert data using JOINs

Let’s add more records to EXAMPLE1 table. We’ll extract 10 highest paying customers and add their firstname and lastname to EXAMPLE1.

We use a subquery in the above INSERT statement. Then, this subquery JOINs ORDER and ORDERLINE tables and extracts 10 customers (customer numbers) who have spent most money. CUSTOMER table is, thereafter, joined to this subquery to extract names of the customers. Those names are inserted in EXAMPLE1.

Hopefully, the examples above will help you build queries in SQL and execute over ODBC to insert data into OpenEdge database tables.

See more articles on working with OpenEdge using SQL here: Modern web applications and API with OpenEdge, SQL, ODBC and PHP