Q
Problem solve Get help with specific problems with your technologies, process and projects.

Inserting values from one table into another, with another set of values

How can I insert values from one table to another table, along with a set of other values? I have tables 'Employee' and 'Loan':

 Employee Table Structure:
 -------------------------
 Employee No.
 BasicPay

 Loan Table Structure:
 -------------------------
 Employee No.
 BasicPay
 LoanAmount, and some other fields.
 
I want to insert EmployeeNo., BasicPay from 'Employee' table into 'Loan' table, and at the same time I want to insert a value for 'LoanAmount' column of 'Loans' table. How can I do this in a single query?

You need to at least know the "connecting" piece of information between the two tables in order to do this. It appears that in your example, the connecting column is the Employee No column. Therefore, in order to get the BasicPay value from the Employee table to populate the Loan table, you'd need to know the employee number when you are inserting into Loan.

I created the following example to answer your question:

EMP Table
       ENO   BASICPAY
---------- ----------
         1      15000
         2      20000
         3      25000
To insert a row into the loan table for employee number 3 with a loan amount of 100,000:
INSERT INTO loan 
SELECT eno, basicpay, 100000
  FROM emp
 WHERE eno = 3 ;
Results in:
LOAN Table
       ENO   BASICPAY    LOANAMT
---------- ---------- ----------
         3      25000     100000
         
You could also do this in a before INSERT trigger to populate the basicpay column with the appropriate value as follows:
CREATE OR REPLACE TRIGGER i_loantab_trigger 
BEFORE INSERT ON loan
FOR EACH ROW
DECLARE
   v_basicpay	loan.basicpay%TYPE ;
BEGIN
   SELECT basicpay
     INTO v_basicpay
     FROM employees
    WHERE eno = :new.eno ;
    
   :new.basicpay := v_basicpay ;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
    :new.basicpay := 0 ;
END ;
/
Then when you issue the following INSERT statement:
INSERT INTO loan (eno, loanamt) VALUES (2, 150000) ;
You get:
 
LOAN Table
       ENO   BASICPAY    LOANAMT
---------- ---------- ----------
         2      20000     150000
Hope that helps!

For More Information


Dig Deeper on Oracle and SQL

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close