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.

Inserting data into an OpenEdge table with SQL

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

CREATE TABLE example1 (
	id INTEGER PRIMARY KEY,
	firstname varchar(100) NOT NULL,
	lastname varchar(100) NOT NULL,
	gender char(1) DEFAULT 'M',
	dob DATE
);

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

-- explicitly provide all fields and values
INSERT INTO example1 (id, firstname, lastname, gender, dob)
VALUES  (1, 'John', 'Smith', 'M', '1950-01-31');

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

-- implicitly provide all fields and values  
INSERT INTO example1
VALUES  (2, 'Darla', 'Anderson', 'F', '1973-12-17');

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:

INSERT INTO example1 (id, firstname, lastname)
VALUES  (3, 'Xing', 'Park');

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

SELECT * FROM example1;

ID |FIRSTNAME |LASTNAME |GENDER |DOB        |
---|----------|---------|-------|-----------|
1  |John      |Smith    |M      |1950-01-31 |
2  |Darla     |Anderson |F      |1973-12-17 |
3  |Xing      |Park     |M      |           |

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:

CREATE SEQUENCE seq_example1_id INCREMENT BY 1, START WITH 3, NOCYCLE;

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.

-- use a sequence to insert DATA
INSERT INTO example1
VALUES (seq_example1_id.NEXTVAL, 'Tony', 'Ronaldo', NULL, NULL);

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.

SELECT * FROM example1;

ID |FIRSTNAME |LASTNAME |GENDER |DOB        |
---|----------|---------|-------|-----------|
1  |John      |Smith    |M      |1950-01-31 |
2  |Darla     |Anderson |F      |1973-12-17 |
3  |Xing      |Park     |M      |           |
4  |Tony      |Ronaldo  |       |           |

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:

-- try to not mix sequence and manual ID insertion
-- this will work
INSERT INTO example1
VALUES (5, 'Jimmy', 'Pickering', 'M', '2000-10-12');

-- at this stage, sequence is at 4
-- calling NEXTVAL will return 5. But, 5 is already in the DB.
-- So, primary key is violated and this statement fails
-- However, sequence will be incremented
INSERT INTO example1
VALUES (seq_example1_id.NEXTVAL, 'Tony', 'Ronaldo', NULL, NULL);

-- trying again will succeed since NEXTVAL will be 6
INSERT INTO example1
VALUES (seq_example1_id.NEXTVAL, 'Tony', 'Ronaldo', NULL, NULL);

• 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:

INSERT INTO example1 (id, firstname, lastname)
SELECT
	seq_example1_id.nextval,
	substr(contact, 1, instr(contact, ' ')),
	substr(contact, instr(contact, ' ')+1, 100)
FROM customer
FETCH FIRST 10 ROWS only;

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.

INSERT INTO example1 (id, firstname, lastname)
SELECT
	seq_example1_id.nextval,
	substr(contact, 1, instr(contact, ' ')),
	substr(contact, instr(contact, ' ')+1, 100)
FROM customer
INNER JOIN (

	-- top 10 highest paying customers
	SELECT o.custnum, sum(line.extendedprice)
	FROM orderline line
	INNER JOIN "order" o ON line.ordernum = o.ordernum
	GROUP BY o.custnum
	ORDER BY 2 DESC
	FETCH FIRST 10 ROWS ONLY

) bigcustomers ON bigcustomers.custnum = customer.custnum;

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