CODEWITHSUNDEEP
sqloracle-databasesql-insertcreate-table
thenewsboy
thenewsboy

Reputation: 1

SQL query fetching data and insert to db

Table employeeAccount:

CREATE TABLE employeeAccount
( 
    employAccID NUMBER(2),
    emplyUsername VARCHAR(20),
    emplyFirstName VARCHAR(20),
    emplyLastName VARCHAR(20),
)

INSERT INTO employeeAccount(employAccID ,emplyUsername ) VALUES (1,'TestAccount1')
INSERT INTO employeeAccount(employAccID ,emplyUsername ) VALUES (2,'TestAccount2')

Table JobRole:

CREATE TABLE Jobrole
( 
    jobNo NUMBER(2),
    jobName VARCHAR(20),
)

INSERT INTO Jobrole(jobNo ,jobName) VALUES (001,'Admin')
INSERT INTO Jobrole(jobNo ,jobName) VALUES (002,'CEO')

And here is my employeeJob code:

CREATE TABLE employeeJob
(
  empid NUMBER(2),
  empjob NUMBER(2),
  CONSTRAINT pk_employeeJob PRIMARY KEY(empid,empjob),
  CONSTRAINT fk_empassignjob1 FOREIGN KEY(empid) REFERENCES employeeAccount(employAccID),
 CONSTRAINT fk_empassignjob2 FOREIGN KEY(empjob) REFERENCES Jobrole(jobNo)
)

Below is the insertion query:

INSERT INTO employeeJob(empid,empjob)
VALUES (1,'001')

Any idea how do I make it automatic take all the data like emplyFirstName, emplyLastName from employeeAccount table & insert to employeeJob. How should I do that to make it auto insert other data that existing from employeeAccount duplicate to employeeJob table?

Upvotes: 0

Views: 90

Answers (2)

Cujoey
Cujoey

Reputation: 147

You can create a trigger on the employeejob table on insert like

Create trigger trgGetOtherDetails
On employeejob AFTER INSERT -- the AFTER key word is Necessary
AS
BEGIN
Declare @emplyFirstname   varchar(30), 
@emplyLastname   varchar(30),
@empId      varchar(5)

select @empId = empId from inserted

Select @emplyFirstname = emplyFirstname, @emplyLastname = emplyLastname from employeeAccount where employAccID = @empId

Update employeejob 
Set emplyFirstname = @emplyFirstname, 
emplyLastname = @emplyLastname 
Where empId = @empId

END

Upvotes: 0

Rohan Pillai
Rohan Pillai

Reputation: 967

Yes you can,

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

You can populate table2 automatically from table1. If you want all the records, ignore the WHERE condition. However, if you want to automatically populate only certain records from table1 to table2 write the condition in WHERE statement.

Upvotes: 1

Related Questions