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!
This question is related to
sql
oracle
database-design
insert-into
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;
/
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.
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.
Source: Stackoverflow.com