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

User · Answer

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.

User · Answer

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

User · Answer

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

[sql] Inserting values into tables Oracle SQL

Examples related to sql

Examples related to oracle

Examples related to database-design

Examples related to insert-into