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

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

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

[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