DESCRIPTION
Is there a way to bulk load data into Botics5? I have an excel table with Elcode and data for a field in the est.extend2 tab. Do I have to enter these in by hand (2000 records!)?
RESOLUTION
You don't have to load these by hand. You can use your excel spreadsheet to create insert statements that you can cut and paste into SQL+ while logged in as the biotics_dlink user and run. Here's the general syntax for an insert statement:
insert into NAME_OF_TABLE (column1, column 2, etc) values (value for column1, value or column2, etc.);
What you want is one insert statement for each row you want to add to a table. You want to have your spreadsheet set up like this:
| A |
B |
C |
D |
E |
| insert into NAME_OF_TABLE (column1, column2) values ( | value for column 1 |
, |
value for column 2 |
); |
In this example, columns B and D of the spreadsheet would have the data in them. You would fill down the other columns so that each row is a separate insert statement ended by a semicolon. You can then copy and paste directly into SQL+ and run the statements.
Very important: when you're finished, you must commit the inserts with a commit statement.
If you have many lines (more then 25) you can also save the excel file as unformatted text and run it as a SQL script. See the Building a SQL Script Solution.
Also very important: depending on what table you are updating, you may need to supply a primary key value. Most of the business tables in the database have triggers on them that will automatically supply a primary key value when a row is inserted. Example, ELEMENT_GLOBAL, EO, SITE, MA, etc. So if you're updating a business table that has a trigger on it, you don't have to worry about this. BUT: if you are updating a table that doesn't have a trigger, like a domain table, you must supply the PK value yourself.
In Biotics 5, all primary keys are set by Oracle sequences. If you were familiar with the next_id in the NEXT_SEQ table in Biotics 4, forget about it! It no longer controls the primary key values. When writing insert statements, use the getnextseq function, as shown below, to set the primary key of a table in order to prevent out of sequence errors in Biotics 5. See the Solution: GETNEXTSEQ function for further details.
insert into D_COUNTY (d_county_id, d_state_id, county_cd, county_name, Display_value)
values (getnextseq('D_COUNTY'),
1,
'ABCDEF',
'County of Carol',
'ABCDEF - County of Carol');
The statement above will get the next primary key from the sequence and put it in the d_county_id column, then insert the literals in the other columns.
Then commit this update and you should be set.
You'll probably have to finagle around with the spreadsheet to get the insert statement just right, but it beats manual input. And once you know how to do this, you can do it over and over again.
See the Solution entitled Excel macros for use in formatting batch update spreadsheets.
See the Solution entitled Batch create new EGR records, update EGT to reflect new/updated rank, create new TAXON_GBL_RANK_CHANGES, update ENT to reflect new/updated rank
See the Solution entitled Batch create new EST records