Inserting values into tables Oracle SQL

Question

I'm trying to insert values into an 'Employee' table in Oracle SQL. I have a question regarding inputting values determined by a foreign key:

My employees have 3 attributes that are determined by foreign keys: State, Position, & Manager. I am using an INSERT INTO statement to insert the values and manually typing in the data. Do I need to physically look up each reference to input the data or is there a command that I can use? E.g.

INSERT INTO Employee 
(emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager)
VALUES 
(001, "John Doe", "1 River Walk, Green Street", 3, 5, 1000)

This should populate the employee table with (John Doe, 1 River Walk, Green Street, New York, Sales Executive, Barry Green). New York is state_id=3 in the State table; Sales executive is position_id=5 in the positions table; and Barry Green is manager_id=1000 in the manager table.

Is there a way in which I can input the text values of the referenced tables, so that Oracle will recognise the text and match it with the relevant ID? I hope this question makes sense will be happy to clarify anything.

Thanks!

Nir Alfasi · Accepted Answer · 2012-05-02T21:50:09.617

8

You can expend the following function in order to pull out more parameters from the DB before the insert:

--
-- insert_employee  (Function) 
--
CREATE OR REPLACE FUNCTION insert_employee(p_emp_id in number, p_emp_name in varchar2, p_emp_address in varchar2, p_emp_state in varchar2, p_emp_position in varchar2, p_emp_manager in varchar2) 
RETURN VARCHAR2 AS

   p_state_id varchar2(30) := '';
 BEGIN    
      select state_id 
      into   p_state_id
      from states where lower(emp_state) = state_name;

      INSERT INTO Employee (emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager) VALUES 
                (p_emp_id, p_emp_name, p_emp_address, p_state_id, p_emp_position, p_emp_manager);

    return 'SUCCESS';

 EXCEPTION 
   WHEN others THEN
    RETURN 'FAIL';
 END;
/

edited May 02 '12 at 21:50

answered May 02 '12 at 21:45

Nir Alfasi

53,191
11
86
129

1

Do you mean `into p_state_id`? – MatBailie May 02 '12 at 21:46
This is slightly more advanced than what I have covered up to now but I understand the functions and exactly what you are doing. I like the error checking too for the added security. Thanks for this – adohertyd May 02 '12 at 21:52
For stuff like this I prefer to create a function/procedure and then just call it with the parameters, otherwise a rather simple insert sql might become a monster... – Nir Alfasi May 02 '12 at 21:52
Just thinking that the use of a function and the exception handling are a little unnecessary here. If this were created as a procedure and an exception occurred then this exception would propagate to the calling procedure.... and you would also know what the exception was! – Mike Meyers May 02 '12 at 22:22
@MikeMeyers - I suspect this may be a case of learning that from experience. – MatBailie May 03 '12 at 08:29
@MikeMeyers The reason I chose a function over a procedure is that he'll be able to return a value. That's the way I like to do it. But, I agree that you have a point and this could be implemented by a procedure as well. – Nir Alfasi May 03 '12 at 19:40

Quassnoi · Answer 2 · 2012-05-02T21:57:46.247

7

INSERT
INTO    Employee 
        (emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager)
SELECT  '001', 'John Doe', '1 River Walk, Green Street', state_id, position_id, manager_id
FROM    dual
JOIN    state s
ON      s.state_name = 'New York'
JOIN    positions p
ON      p.position_name = 'Sales Executive'
JOIN    manager m
ON      m.manager_name = 'Barry Green'

Note that but a single spelling mistake (or an extra space) will result in a non-match and nothing will be inserted.

edited May 02 '12 at 21:57

answered May 02 '12 at 21:42

Quassnoi

413,100
91
616
614

2

It should probably be something like `ON manager_name = 'Barry Green'`, not `manager_id = ...` (because that's what should be retrieved) – May 02 '12 at 21:46

score 4 · Answer 3 · answered May 02 '12 at 21:41

4

You can insert into a table from a SELECT.

INSERT INTO
  Employee (emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager)
SELECT
  001,
  'John Doe',
  '1 River Walk, Green Street',
  (SELECT id FROM state WHERE name = 'New York'),
  (SELECT id FROM positions WHERE name = 'Sales Executive'),
  (SELECT id FROM manager WHERE name = 'Barry Green')
FROM
  dual

Or, similarly...

INSERT INTO
  Employee (emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager)
SELECT
  001,
  'John Doe',
  '1 River Walk, Green Street',
  state.id,
  positions.id,
  manager.id
FROM
  state
CROSS JOIN
  positions
CROSS JOIN
  manager
WHERE
      state.name     = 'New York'
  AND positions.name = 'Sales Executive'
  AND manager.name   = 'Barry Green'

Though this one does assume that all the look-ups exist. If, for example, there is no position name 'Sales Executive', nothing would get inserted with this version.

answered May 02 '12 at 21:41

MatBailie

83,401
18
103
137

Thanks that's great. I'm running this in a script in Oracle 10g XE so I think it will throw out some errors if no record is found. Is there a 'fail safe' method or is it just a case of being vigilant? – adohertyd May 02 '12 at 21:50
@adohertyd - It depends on the behaviour you want. If one of the names doesn't exist, should it create the record with a NULL foreign key id? *(That's what the first one will do.)* Or would you prefer to catch that scenario and report back a problem? – MatBailie May 02 '12 at 21:52
I would prefer a report back. Just for error checking. I think Alfasin has covered it in his function below though – adohertyd May 02 '12 at 21:54

Inserting values into tables Oracle SQL

3 Answers3