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